Solved: Create new rows when there are no sales - Qlik Community

sdaniels0606 · ‎2017-01-13

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 · ‎2017-01-16

Sorry use this..

YEAR & Date(Date#(MONTH,'MM'),'MMMM') & SHOP & [Product Type] as Key

View solution in original post

MK_QSL · ‎2017-01-13

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

Report Inappropriate Content · ‎2017-01-13

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

sdaniels0606 · ‎2017-01-13

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?

sdaniels0606 · ‎2017-01-13

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

Anonymous · ‎2017-01-13

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

Report Inappropriate Content · ‎2017-01-13

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

Report Inappropriate Content · ‎2017-01-13

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

Anonymous · ‎2017-01-13

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.

Anonymous · ‎2017-01-13

yeah that's what replied to the previous comment.