Creating zero sales for items not sold

[hopkinsc]

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.

[sunny_talwar]

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;

[sunny_talwar]

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;

[hopkinsc]

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?

[sunny_talwar]

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?

[hopkinsc]

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

[sunny_talwar · Accepted solution]

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;

[hopkinsc]

Thats perfect, exactly what i need!

Thanks Sunny