topic Re: Create new rows when there are no sales in QlikView

Create new rows when there are no sales

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

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.

Re: Create new rows when there are no sales

MK_QSL — Fri, 13 Jan 2017 13:32:29 GMT

Provide some sample data. I will do this for you.

Re: Create new rows when there are no sales

Fri, 13 Jan 2017 13:38:17 GMT

You can combine the Product types of the product type that are sold and the one's that aren't and then enable

the option "view Null values" in de object properties.

To change the NULL value into €0, you can an IF function in an expression to show the NULL values as €0.

I hope this can help you further,

With kind regards,

Ivo

Re: Create new rows when there are no sales

sdaniels0606 — Fri, 13 Jan 2017 13:45:49 GMT

Thanks for your response.

How am i going to combine the sold ones with the ones that aren't if I dont have any record of the latter?

Re: Create new rows when there are no sales

sdaniels0606 — Fri, 13 Jan 2017 13:47:00 GMT

Ive got the personal edition, I cant open files that I havent made.

Re: Create new rows when there are no sales

Anonymous — Fri, 13 Jan 2017 13:55:16 GMT

please uncheck the Supress Zero value in the properties of the sheet object.

Re: Create new rows when there are no sales

Fri, 13 Jan 2017 13:56:58 GMT

Hmm I was just overthinking my answer and came to the conclusion that this only

works with a yes or a no for seeing if a product is sold. Because with this, you can load

in an extra line in your table witch says " 'sold' as Sold" and then Left join a new table, which loads in

a file that contains all available products, on that table. In that way you can create a table box

which shows the items that are sold and those that aren't. But I don't think it will work if you have

different sales amounts for each product.

Maybe you can try if it works, but I don't think it will.

Good luck anyway!

Ivo

Re: Create new rows when there are no sales

Fri, 13 Jan 2017 13:57:48 GMT

Its not just that, its mostly because the items that arent sold, aren't logged and they wont be shown in the datafiles

Re: Create new rows when there are no sales

Anonymous — Fri, 13 Jan 2017 13:58:51 GMT

try using Left Join or Right Join depending on the loading hierarchy and you will get all the product names and you will get the facts for the sold ones and NULL for the rest. I hope this works.

Re: Create new rows when there are no sales

Anonymous — Fri, 13 Jan 2017 14:00:35 GMT

yeah that's what replied to the previous comment.

Re: Create new rows when there are no sales

MK_QSL — Fri, 13 Jan 2017 14:23:22 GMT

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

Re: Create new rows when there are no sales

simon_minifie — Fri, 13 Jan 2017 14:32:30 GMT

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

Re: Create new rows when there are no sales

sdaniels0606 — Fri, 13 Jan 2017 15:13:26 GMT

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

Re: Create new rows when there are no sales

sdaniels0606 — Fri, 13 Jan 2017 15:15:34 GMT

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

Re: Create new rows when there are no sales

sdaniels0606 — Fri, 13 Jan 2017 15:17:07 GMT

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

Re: Create new rows when there are no sales

MK_QSL — Fri, 13 Jan 2017 15:19:36 GMT

Export to Excel from SQL and post it here.

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

Re: Create new rows when there are no sales

sdaniels0606 — Fri, 13 Jan 2017 15:22:23 GMT

Okey, ill post it as soon as possible.

Re: Create new rows when there are no sales

sdaniels0606 — Sat, 14 Jan 2017 13:39:01 GMT

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.

Re: Create new rows when there are no sales

MK_QSL — Sat, 14 Jan 2017 15:00:29 GMT

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;

Re: Create new rows when there are no sales

MK_QSL — Mon, 16 Jan 2017 09:09:27 GMT

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.