Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik gurus,
On a measure in bar chart I have configured following expression:
Count({$<TYPENAME={'Workstation','Windows Workstation'}, RESOURCENAME={'ec-*'}, RESOURCENAME={'*-w', '*-ep', '*-eb'}, RESOURCESTATEID={'2'}, ACQUISITIONDATE-={'-'}>} Distinct RESOURCENAME)
I have thought that it will work like where clause from my query below:
select ... from ...
where typename in ('Workstation','Windows Workstation')
and resourcename like 'ec-%'
and (resourcename like '%-w' or resourcename like '%-ep' or resourcename like '%-eb')
and resourcestateid=2
and acquisitiondate is not null
My sql query in MS SQL gives me 5 records, Qlik measure gives 6 records.
I have checked and appeared that this one additional record calculated in qlik has RESOURCENAME='szk-1668-w'.
This record is not counted in sql query, because condition "...resourcename like 'ec-%' ..." is not fulfilled.
The strangest thing is when I change order of conditions in measure expression to the version below:
Count({$<TYPENAME={'Workstation','Windows Workstation'}, RESOURCENAME={'*-w', '*-ep', '*-eb'}, RESOURCENAME={'ec-*'}, RESOURCESTATEID={'2'}, ACQUISITIONDATE-={'-'}>} Distinct RESOURCENAME)
bar chart calculates 17 records !
I have double checked, in sets of records where there are only records like 'ec-%', results of calculations in qlik are OK.
Could someone tell me what is wrong in my expression for measure?
Why order of conditions has such significant impact on calculation?
Or maybe I look at the order of conditions like in sql query and it is wrong?
Any help appreciated.
How about this
Count({$<TYPENAME = {'Workstation','Windows Workstation'}, RESOURCENAME = {'ec-*-w', 'ec-*-ep', 'ec-*-eb'}, RESOURCESTATEID = {'2'}, ACQUISITIONDATE -= {'-'}>} DISTINCT RESOURCENAME)
How about this
Count({$<TYPENAME = {'Workstation','Windows Workstation'}, RESOURCENAME = {'ec-*-w', 'ec-*-ep', 'ec-*-eb'}, RESOURCESTATEID = {'2'}, ACQUISITIONDATE -= {'-'}>} DISTINCT RESOURCENAME)
Null values are not actually '-' that's just how they display, so can't test for '-'
use:
ACQUISITIONDATE = {*}
This indicates any value, so will exclude nulls.