Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Shrikant
Contributor III
Contributor III

Show only missing/NULL values in chart

Hello,

I have a table of GRN, Vendor and Purchaser.

GRN and vendor linked on Vendor code

Vendor and Purchaser are linked on Purchaser code.

Vendor is linked a Purchaser in Vendor table(few vendors DO NOT have purchaser assigned)

Please note these are separate tables and not inner/left joined but linked on Keys(association)

I am trying to get purchaser-wise (purchaser as dimension) on time delivery % and is working fine with all purchaser and NULL value.

But I want 2 tables...

  1. First all rows without NULL(missing) Purchaser --> This is working fine
  2. Other with only NULL row and %. --> shows - and so no data

I was able to do this in QlikView with =If(Len(Trim([Purchaser])) = 0, 'NA', Null()), but same is not working in QlikSense...

I searched a lot everyone gives the same able solution but its not working for me... am I missing anything?

I also tried isnull, ='' 😞

Shrikant_0-1649827498387.png

Thanks

Shrikant

 

Labels (2)
7 Replies
Kashyap_R
Partner - Specialist
Partner - Specialist

Hi 

Try this

=If(Len(Trim([Purchaser])) = 0 or isnull(Purchaser), 'NA', 'Existing')

if this gives you correct answer then you can limit by Existing and NA by using if conditions

Hope this works for you

Regards 

kashyap.R

Thanks and Regards
Kashyap.R
Shrikant
Contributor III
Contributor III
Author

Thanks for suggestion.

reply is edited

It works when I use left join on all tables and create only one table instead of 3 (with association) else the same issue persists for all solution 😞

I also tried istext but same issue

 

Thanks

Shrikant

Kashyap_R
Partner - Specialist
Partner - Specialist

Hi

Can u attach sample qvf so that anyone can try

 

Thanks and Regards
Kashyap.R
Shrikant
Contributor III
Contributor III
Author

Hello Kashyap,

QVF attached. In below image I want the data only for missing Purchasers

Shrikant_1-1650446462064.png

For your info it works perfect when i apply left join.

Thanks

Shrikant

Kashyap_R
Partner - Specialist
Partner - Specialist

Hi 

PFA  Output is in the first sheet in the app hope this helps 

Regards 

Kashyap.R

Thanks and Regards
Kashyap.R
Shrikant
Contributor III
Contributor III
Author

Hello Kashyap,

I don't see any change 😞

Shrikant_0-1650944027250.png

 

Thanks

Shrikant

 

vinieme12
Champion III
Champion III

You really need to replace null values in your data

 

or try below Dimension

=aggr(If(Len(Trim([Purchaser])) <1, 'NA',Purchaser)<Purchaser)

 

MEasure

= sum({<Purchaser={"=len(Purchaser)<1"}}> YourMEasureField)

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.