topic Re: Creating zero sales for items not sold in QlikView

Creating zero sales for items not sold

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

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.

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.

Re: Creating zero sales for items not sold

sunny_talwar — Wed, 10 Oct 2018 11:28:17 GMT

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;

Re: Creating zero sales for items not sold

sunny_talwar — Wed, 10 Oct 2018 11:29:04 GMT

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;

Re: Creating zero sales for items not sold

hopkinsc — Wed, 10 Oct 2018 11:35:57 GMT

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?

Re: Creating zero sales for items not sold

sunny_talwar — Wed, 10 Oct 2018 11:38:03 GMT

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?

Re: Creating zero sales for items not sold

hopkinsc — Wed, 10 Oct 2018 11:55:37 GMT

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

Re: Creating zero sales for items not sold

sunny_talwar — Wed, 10 Oct 2018 12:05:58 GMT

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;

Re: Creating zero sales for items not sold

hopkinsc — Wed, 10 Oct 2018 12:09:27 GMT

Thats perfect, exactly what i need!

Thanks Sunny