Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ivo_petrov
New Contributor

Show missing value items only

Merry Christmas to all of you,
I'm pretty new in here and with the Qlik Sense as well, so I'd like to say sorry in advance, if my questions looks stupid.
Here is my issue.

I have 2 tables, connected by ProductID field.

Table 1: PRODUCT_LIST

ProductID

ProductName

Table 2: SALES

ProductID

Sales

Client

I'd like to see which products haven't been bought by the selected Client, but I have no idea how that should be done :/

Could somebody help me, please!

6 Replies
pradosh_thakur
Valued Contributor III

Re: Show missing value items only

1: filter with dimension Client

2: KPI/Textbox with =concat({<Client=e(Client)>} ProductName,',')  or  =concat( distinct {<Client=e(Client)>} ProductName,',')

let me know if your requirement is something else.

p.s. Have tried it in qlikview

ivo_petrov
New Contributor

Re: Show missing value items only

Hello pradosh and Happy New Year! Let the 2018 be more fruitful and prosperiuos one!

Thank you for your suggestion. It works flawlessly, but I missed the fact, that dealing with hundreds of products it's pretty hard to see what you're looking for. So, in order to be more useful, the data should be organized in groups.

I've tried to do it, but I failed. So, please have a look at it.

Here are the tables with the new fields added (in bold)


Table 1: PRODUCT_LIST

ProductID

ProductName

ProductGroup

TypeOfProduct

Table 2: SALES

ProductID

Sales

Client

After selecting a Client, I'd like to select a ProductGroup and TypeOfProduct and then it's pretty clear what the Client doesn't buy.

I hope, I have described it clear enough.

Thank you in advance!

pradosh_thakur
Valued Contributor III

Re: Show missing value items only

HI

Happy new Year!!

You want my help in creating two new fields in script ? if yes than whats the logic for it ?

ivo_petrov
New Contributor

Re: Show missing value items only

Hello again,

No, I don't need your help for creating two more fields. I failed to describe what I'm trying to do.

These two fields exists in the table already. I use'em to reduce the number of shown products, using these two category fields - ProductGroup and TypeOfProduct.

Let's imagine that the table holds some automotive data as follows:


ProductID / ProductName / ProductGroup / TypeOfProduct

01 / Volvo / cars / sport wagon

02 / Volvo / truck / heavy truck

03/ Audi / cars / sport wagon

04 / Audi / cars / SUV

... and so on.

What I'm trying to do, but I can't, is to see which products haven't been bought by the selected Client, but in order to reduces to number of shown products, I need to filter the results using the other 2 category fileds, as well.

Let's assume that a client ABC has bought the car #04 - Audi / cars / SUV.

I'd like to see what else he can buy, but not all the other references. Just those that are made by Audi (ProductName) and are not trucks i.e. cars (ProductGroup). Or all the cars (ProductGroup) which are sport wagon (TypeOfProduct).

pradosh_thakur
Valued Contributor III

Re: Show missing value items only

stalwar1‌  a little help here. was trying the e() with the way you showed the other day but could not.

MVP
MVP

Re: Show missing value items only

I am not entirely sure I understand the requirement here.. would you be able to give few more extra rows of data and then provide the output you expect to see within QlikView?