Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sdaniels0606
Contributor III
Contributor III

Create new rows when there are no sales

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.

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Sorry use this..

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

View solution in original post

31 Replies
MK_QSL
MVP
MVP

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

Not applicable

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

anirbanpatra
Contributor III
Contributor III

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

Not applicable

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

Not applicable

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

anirbanpatra
Contributor III
Contributor III

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.

anirbanpatra
Contributor III
Contributor III

yeah that's what  replied to the previous comment.