Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I want to create records for items not sold in my sales table.
Basically what i have is a sales file that contains 3 years of sales, new sales are appended to the file at the end of each period.
What i need is to create records per period for items that have not sold but have sold previously.
eg
period, customer, item, sales
201901, A, ABC, 50
201901, B, ABC, 10
201902, A, ABC, 40
201903, A, ABC, 30
201903, B, ABC, 60
201904, B, ABC, 40
201905, A, ABC, 50
201905, B. ABC, 100
So in the above, item ABC didnt sell in 201902 for customer B and didnt sell in period 201904 for customer A.
So i would want is to craete rows in the table for:
201902, B, ABC, 0
201904, A, ABC, 0
I thought i would just do a distict load of customer and item and concatenate 0 as Sales Value onto the table but i can't get it to work.
Can anyone help please?
I have attached a sample.
Is it a problem if we populate 0s before the first date when a customer, item combination shows up... for example... if we look a Cust ID 24269 and Item 32131.... do you only want to add 201811 with 0s or having them since 201801 works too?
If the above is good, you can try this
Data:
LOAD *,
YearPeriod&[Cust ID]&Item as Key;
LOAD YearPeriod,
[Cust ID],
Item,
[Sales Value],
[Sales Volume]
FROM
Sample.xlsx
(ooxml, embedded labels, table is Sheet1);
Temp:
LOAD DISTINCT YearPeriod
Resident Data;
Join (Temp)
LOAD DISTINCT [Cust ID],
Item
Resident Data;
//Join (Temp)
//LOAD DISTINCT Item
//Resident Data;
Concatenate (Data)
LOAD *,
0 as [Sales Value],
0 as [Sales Volume]
Resident Temp
Where not Exists(Key, YearPeriod&[Cust ID]&Item);
DROP Table Temp;
Try this
Data:
LOAD *,
period&customer as Key;
LOAD * INLINE [
period, customer, item, sales
201901, A, ABC, 50
201901, B, ABC, 10
201902, A, ABC, 40
201903, A, ABC, 30
201903, B, ABC, 60
201904, B, ABC, 40
201905, A, ABC, 50
201905, B, ABC, 100
];
Temp:
LOAD DISTINCT period
Resident Data;
Join (Temp)
LOAD DISTINCT customer
Resident Data;
Concatenate (Data)
LOAD *
Resident Temp
Where not Exists(Key, period&customer);
DROP Table Temp;
Actually this, since you want 0
Data:
LOAD *,
period&customer as Key;
LOAD * INLINE [
period, customer, item, sales
201901, A, ABC, 50
201901, B, ABC, 10
201902, A, ABC, 40
201903, A, ABC, 30
201903, B, ABC, 60
201904, B, ABC, 40
201905, A, ABC, 50
201905, B, ABC, 100
];
Temp:
LOAD DISTINCT period
Resident Data;
Join (Temp)
LOAD DISTINCT customer
Resident Data;
Concatenate (Data)
LOAD *,
0 as sales
Resident Temp
Where not Exists(Key, period&customer);
DROP Table Temp;
Hi Sunny,
Thanks for your reply, The resulting table gives me nulls in the item field. I need the item populated too, what do i need to change to get that populating correctly?
In the above case you only have a single item, can you have multiple items? Would you be able to provide a sample and it's output when you have multiple period, multiple customer and multiple items?
Hi Sunny,
I have attached my an extract of real data (scrambled).
examples of missing items in a period are:
customer, Item, missing Period
20100, 32131, 201902
24269, 32131, 201811
24269, 5600, 201901
So the above i would want to see rows with the fields populated but 0 for Sales/Volume
Thanks
Is it a problem if we populate 0s before the first date when a customer, item combination shows up... for example... if we look a Cust ID 24269 and Item 32131.... do you only want to add 201811 with 0s or having them since 201801 works too?
If the above is good, you can try this
Data:
LOAD *,
YearPeriod&[Cust ID]&Item as Key;
LOAD YearPeriod,
[Cust ID],
Item,
[Sales Value],
[Sales Volume]
FROM
Sample.xlsx
(ooxml, embedded labels, table is Sheet1);
Temp:
LOAD DISTINCT YearPeriod
Resident Data;
Join (Temp)
LOAD DISTINCT [Cust ID],
Item
Resident Data;
//Join (Temp)
//LOAD DISTINCT Item
//Resident Data;
Concatenate (Data)
LOAD *,
0 as [Sales Value],
0 as [Sales Volume]
Resident Temp
Where not Exists(Key, YearPeriod&[Cust ID]&Item);
DROP Table Temp;
Thats perfect, exactly what i need!
Thanks Sunny