0

I'm trying to fill each month with all the units and '0' for the missing unit, can anyone help me on this Actual data: 

LOAD * Inline [
Unit,Month,Sold
7511,Sep 2020,13000
7512,Sep 2020,10000
7511,Nov 2020,7500
7513,Dec 2020,5000
7511,Dec 2020,7100
];
Expected:
Unit Month Sold
7511 Sep 2020 13000
7512 Sep 2020 10000
7513 Sep 2020 0
7511 Nov 2020 7500
7512 Nov 2020 0
7513 Nov 2020 0
7511 Dec 2020 7100
7512 Dec 2020 0
7513 Dec 2020 5000
zouhir karim
  • 1

1 Answers1

0

Create a calendar table that includes all your date(Month) values. Then, left join distinct list of your units to populate data. After that left join your existing data and lastly, use load-resident to recall your table write

if( isnull(Sold), 0, Sold) as Sold

Don't forget to drop old table.

Here is a link about creating a calendar table: enter link description here

ilyas
  • 16
  • 2