Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Partner
Partner

Creating zero sales for items not sold

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.

1 Solution

Accepted Solutions
Highlighted

Re: Creating zero sales for items not sold

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?

Capture.PNG

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;

View solution in original post

7 Replies
Highlighted

Re: Creating zero sales for items not sold

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;

Highlighted

Re: Creating zero sales for items not sold

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;

Highlighted
Partner
Partner

Re: Creating zero sales for items not sold

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?

Highlighted

Re: Creating zero sales for items not sold

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?

Highlighted
Partner
Partner

Re: Creating zero sales for items not sold

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

Highlighted

Re: Creating zero sales for items not sold

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?

Capture.PNG

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;

View solution in original post

Highlighted
Partner
Partner

Re: Creating zero sales for items not sold

Thats perfect, exactly what i need!

Thanks Sunny