Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

A way to show alternative/excluded values

Hi all,

Is there a way/ a direct function to show the alternative/excluded values?

if I have 4 dates for example, and a field containg 100 values;

if I select date1;

count(Field) will be 80

if I want to show these 80 fields; I'll just have to use ; concat(Field,',') for example

but what if I wanted to show the 20 excluded fields??

There is the GetExcludedCount function to return thenumber which is 20; but how to show these 20 values?

Thanks

18 Replies
sunny_talwar

Set analysis will not work here... you will need to use Aggr()... give me some time.. I am testing it out

sunny_talwar

Unable to find any easy way to do this.... may be marcowedel‌ might be able to help here

OmarBenSalem
Author

Thank u !

till then, I'll alter things as follow:

Capture.PNG

Capture.PNG

Capture.PNG

pradosh_thakur
Master II
Master II

Hi Omar

try this

its in Qlikview. You have 174 records which will be too much for the method i have adopted. Please let me know if it helps you.

Learning never stops.
sunny_talwar

Good improvisation pradosh_thakur‌, but PurgeChar will purge individual characters and with 174 currencies you will have a hard time assigning a unique value to each of them. I think there should be a better way to do this using ValueLoop in the expression, but I wasn't able to do it. I am sure Marco will have a way to do this.

OmarBenSalem
Author

I kind of found a solution but It works only if I select a date..

create 3 variables:

vTotalCurrencies : =concat(total distinct{<[WS.AsOf_Date]>} [WS.Currency],', ')

vCurrencyPerDate: =aggr(concat( distinct{<[WS.AsOf_Date]>} [WS.Currency],','),[WS.AsOf_Date])

vTotalNum : =SubStringCount(vTotalCurrencies,', ')+1


in the table;

as dimension: =[WS.AsOf_Date] &' Nbr of missing currencies : '& aggr(GetExcludedCount( [WS.Currency]),[WS.AsOf_Date])

as a measure:

=if(len(trim( concat( if(wildmatch(vCurrencyPerDate,'*'&subfield(vTotalCurrencies,', ',ValueLoop(1,vTotalNum,1))&'*'),NULL(),subfield(vTotalCurrencies,', ',ValueLoop(1,vTotalNum,1)) ) ,', ')))=0, 'No missing currencies',

concat( if(wildmatch(vCurrencyPerDate,'*'&subfield(vTotalCurrencies,', ',ValueLoop(1,vTotalNum,1))&'*'),NULL(),subfield(vTotalCurrencies,', ',ValueLoop(1,vTotalNum,1)) ) ,', ')

)



result:

Capture.PNG

Capture.PNG


but the results are false if I select more than a date (or don't select any)

Capture.PNG

sunny_talwar

I thought that the solution I provided also kind of worked after you made a date selection... what is the difference between the initial solution and this new one? (I haven't gone through your solution yet, but just wondering)

OmarBenSalem
Author

Yours does not work in a table:

Capture.PNG

while, as a final result, I want to show every missing currencies in all the dates (without making any selection)

MarcoWedel

Hi,

although I think you should consider extending your data model to perform this task (see: Generating Missing Data In QlikView) one solution to create this table in the front end only could be:

QlikCommunity_Thread_286777_Pic1.JPG

QlikCommunity_Thread_286777_Pic4.JPG

=$(=Concat({1} Aggr('If(not WildMatch(Concat(WS.Currency,'',''),''*'&Only({1} WS.Currency)&'*''),'' '&Only({1} WS.Currency)&' ''&'','')', WS.Currency),'&'))

QlikCommunity_Thread_286777_Pic3.JPG

QlikCommunity_Thread_286777_Pic2.JPG

table1:

LOAD Distinct

     DayName(Today()-Rand()*10) as WS.AsOf_Date,

     Code as WS.Currency

FROM [https://en.wikipedia.org/wiki/ISO_4217] (html, codepage is 1252, embedded labels, table is @2)

While IterNo() <= Rand()*1000+1;

hope this helps

regards

Marco