topic Re: If Not isnull query in count in App Development

If Not isnull query in count

Daryn — Fri, 10 Jun 2022 13:21:51 GMT

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.

Re: If Not isnull query in count

stevedark — Sat, 11 Jun 2022 19:49:08 GMT

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

Re: If Not isnull query in count

vinieme12 — Mon, 13 Jun 2022 05:37:01 GMT

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)

=count ({< VBELN = p({<flag_gt={1}>VBELN )+p({<HasWADAT={1}>VBELN ), WBSTK = {'C'}, LFART ={'XLF'} >} VBELN ) / COUNT ( {<WBSTK = {'C'} , LFART ={'XLF'}>} VBELN)