Create new rows when there are no sales

[sdaniels0606]

Hello all,

I have a date base which have data of the sales from different shops. The columns are Year, Month, Product Type, Shop and Sales.

When a shop hasn't sold anything of a Product Type in a month there is no record of it.

So I need to add/create a new row that includes the Month, the Date, the Product Type, the Shop and the sales which are 0 €.

How can I do that? I am pretty lost. I read https://community.qlik.com/docs/DOC-3786 but I didn't make any progress.

Thanks in advance.

[MK_QSL]

But I can open your file.. can work on your file.. and can post the entire script here...!

[simon_minifie]

Hi Daniel,

The lack of an item or sale will be recorded by its not being there if the shop exists in the data.

Sales:

Load * inline [

  Date, Month, Product Type, Shop, Sales

  01/03/2016, Mar, Shoes, London, 12

  02/03/2016, Mar, Socks, Edinburgh, 10

  07/04/2016, Apr, Laces, Glasgow, 18

  09/04/2016, Apr, Jumpers, Manchester, 2

  23/06/2016, Jun, Pants, Cardiff, 8

]

;

Thanks,

Simon

[sdaniels0606]

But I get the datebase from a SQL database. I cant do that.

[sdaniels0606]

There are like 320000 rows, if I join sales with month QlikView just stops working.

[sdaniels0606]

How can I post the data here? Its all in a SQL Datebase which I import in QlikView.

[MK_QSL]

Export to Excel from SQL and post it here.

Alternatively you can create 10 to 15 lines of sample data and post them here.

[sdaniels0606]

Okey, ill post it as soon as possible.

[sdaniels0606]

In the file attached there are:

• 3 shops: London, Barcelona and Paris
• 3 Types of product: Pants, T-shirt and Sweater

So, in January London has sold every product type but Barcelona hasn't sold Pants and Sweaters and Paris hasn't sold Sweaters. Then I would need to add 3 rows, 2 for Barcelona with 0 sales of Pants and Sweaters and 1 for Paris with 0 sales of Sweaters.

In February Paris hasn't sold anything, thus, I would need to add three rows for every product type. And 1 for Barcelona that hasn't sold T-shirts.

Many thanks.

[MK_QSL]

Data:
Load
  Year & Month & [Product Type] & Shop as Key
  ,*
Inline
[
  Year, Month, Product Type, Shop, Sales
  2016, January, Pants, London, 54
  2016, January, Tshirt, London, 43
  2016, January, Sweater, London, 112
  2016, January, Tshirt, Barcelona, 76
  2016, January, Pants, Paris, 453
  2016, January, Tshirt, Paris, 432
  2016, February, Pants, London, 76
  2016, February, Tshirt, London, 112
  2016, February, Sweater, London, 87
  2016, February, Pants, Barcelona, 110
  2016, February, Sweater, Barcelona, 123
];


TempKey1:
Load Distinct Year Resident Data;
Join
Load Distinct Month Resident Data;
Join
Load Distinct [Product Type] Resident Data;
Join
Load Distinct Shop Resident Data;


Concatenate(Data)
Load *, 0 as Sales Where Not Exists (Key);
Load
  Year & Month & [Product Type] & Shop as Key,
  Year, Month, [Product Type], Shop
Resident TempKey1;


Drop Table TempKey1;
Drop Field Key;

[MK_QSL]

So still you haven't got the right answer?

Let me know what else you are looking for.

If you got your full answer, kindly close the thread by selecting correct answer.