Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Set analysis will not work here... you will need to use Aggr()... give me some time.. I am testing it out
Unable to find any easy way to do this.... may be marcowedel might be able to help here
Thank u !
till then, I'll alter things as follow:
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.
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.
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:
but the results are false if I select more than a date (or don't select any)
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)
Yours does not work in a table:
while, as a final result, I want to show every missing currencies in all the dates (without making any selection)
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:
=$(=Concat({1} Aggr('If(not WildMatch(Concat(WS.Currency,'',''),''*'&Only({1} WS.Currency)&'*''),'' '&Only({1} WS.Currency)&' ''&'','')', WS.Currency),'&'))
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