Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
Hope your Friday is going well and you have a pleasant weekend ahead of you.
I have a working expression that I would like to 'tweak' if possible. The fields are SAP field names.
count ({< VBELN = {"=[WADAT_IST] > [WADAT] "}, WBSTK = {'C'}, LFART ={'XLF'} >} VBELN ) / COUNT ( {<WBSTK = {'C'} , LFART ={'XLF'}>} VBELN)
So a count where a date (WADAT_IST) is compared against WADAT in the above its to see if it is greater than, which means its late (I have an = and a < than version for on time and early). A couple of = '' to check that it is 'C' complete and belongs to a particular delivery group 'XLF'.
I would like if it is possible to check to see if WADAT_IST is populated and if not (ie null) have it included in my count as well.
I hope that makes sense!
Example image of the date fields.
Hi @Daryn
You can write not null in Set Analysis using WADAT_IST*={"*"}
Note that the first * is allowing selections in WADAT_IST to still be considered, the second * is a wildcard which matches any non-null value.
For more details on the first * see:
https://www.quickintelligence.co.uk/qlik-set-analysis-star-equals/
If some or all of the fields appear in a single row in the source data, you can make things much more efficient (and easier to code for) by working out flags in the load script, for example:
if(isnull(WADAT_IST), 0, 1) as HasWADAT,
Also, it is good practice to rename all fields in the load script, so they are human friendly (rather than SAP friendly) in the front end.
Hope that helps,
Steve
Best to create flag fields as @stevedark suggested for both criteria's
if(WADAT_IST] > [WADAT], 1, 0) as flag_gt,
if(isnull(WADAT_IST), 0, 1) as HasWADAT,
but the below should also work
=count ({< VBELN = {"=([WADAT_IST] > [WADAT]) OR len(WADAT_IST)=0"}, WBSTK = {'C'}, LFART ={'XLF'} >} VBELN ) / COUNT ( {<WBSTK = {'C'} , LFART ={'XLF'}>} VBELN)
with flag fields
=count ({< VBELN = {"=flag_gt=1 or HasWADAT=1"}, WBSTK = {'C'}, LFART ={'XLF'} >} VBELN ) / COUNT ( {<WBSTK = {'C'} , LFART ={'XLF'}>} VBELN)
OR
=count ({< VBELN = p({<flag_gt={1}>VBELN )+p({<HasWADAT={1}>VBELN ), WBSTK = {'C'}, LFART ={'XLF'} >} VBELN ) / COUNT ( {<WBSTK = {'C'} , LFART ={'XLF'}>} VBELN)
Hi @Daryn
You can write not null in Set Analysis using WADAT_IST*={"*"}
Note that the first * is allowing selections in WADAT_IST to still be considered, the second * is a wildcard which matches any non-null value.
For more details on the first * see:
https://www.quickintelligence.co.uk/qlik-set-analysis-star-equals/
If some or all of the fields appear in a single row in the source data, you can make things much more efficient (and easier to code for) by working out flags in the load script, for example:
if(isnull(WADAT_IST), 0, 1) as HasWADAT,
Also, it is good practice to rename all fields in the load script, so they are human friendly (rather than SAP friendly) in the front end.
Hope that helps,
Steve
Best to create flag fields as @stevedark suggested for both criteria's
if(WADAT_IST] > [WADAT], 1, 0) as flag_gt,
if(isnull(WADAT_IST), 0, 1) as HasWADAT,
but the below should also work
=count ({< VBELN = {"=([WADAT_IST] > [WADAT]) OR len(WADAT_IST)=0"}, WBSTK = {'C'}, LFART ={'XLF'} >} VBELN ) / COUNT ( {<WBSTK = {'C'} , LFART ={'XLF'}>} VBELN)
with flag fields
=count ({< VBELN = {"=flag_gt=1 or HasWADAT=1"}, WBSTK = {'C'}, LFART ={'XLF'} >} VBELN ) / COUNT ( {<WBSTK = {'C'} , LFART ={'XLF'}>} VBELN)
OR
=count ({< VBELN = p({<flag_gt={1}>VBELN )+p({<HasWADAT={1}>VBELN ), WBSTK = {'C'}, LFART ={'XLF'} >} VBELN ) / COUNT ( {<WBSTK = {'C'} , LFART ={'XLF'}>} VBELN)