topic Re: Trying to create a Flag in QLikview in QlikView

Trying to create a Flag in QLikview

dunnalahk123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I Created one Flag Called ASDPFlag as per below Script and it is working fine as expected.

based IF (IsNull([ASDPID]) or Len([ASDPID]) = 0,'1' ,'0') as ASDPFlag,

But now i want to modify that ASDPFlag because my calculation will depends on STID Field as well .

From above snapshot one STID has 3 Rows due to Distinct MDCP Organization ID

now what i want is ASDP Flag should be Flag as "0" for another 2 Rows as well because at least ASDPID is available once per Existing STID.

if ASDPID is not available at least once Per STID then we have to Flag it as 1.

So how can we tweak below logic

IF (IsNull([ASDPID]) or Len([ASDPID]) = 0,'1' ,'0') as ASDPFlag,

Can some one help please.

Best Regards,

Re: Trying to create a Flag in QLikview

rubenmarin — Tue, 13 Jun 2017 19:18:37 GMT

Hi, you can do a sorted load and check previous values using peek():

LOAD ...

If(Peek([ST ID])=[ST ID], Peek(ASDPID), IF (IsNull([ASDPID]) or Len([ASDPID]) = 0,'1' ,'0')) as ASDPFlag,

...

Order by [ST ID], ASPID;

Another option is creating a check feld for [ST ID] whit ASDPID:

CheckASPID:

LOAD [ST ID] as chkSTID

From/resident ... where IsNull(ASDPID) or Len(Trim(ASDPID))=0

YourTable:

LOAD ...

If(Exists('chkSTID', [ST ID]), '1' ,'0') as ASDPFlag,

...

Order by [ST ID], ASPID;

Note that there are non visible characters that can be counted, maybe want to use :

Len(Trim([ASPID]))>0

You can also do this with mappings.

Re: Trying to create a Flag in QLikview

dunnalahk — Thu, 15 Jun 2017 20:22:23 GMT

Hi,

Sorry for coming back late

I tried but still no luck , i Guess its trying to calculate from Row Calculation from back end or i may be doing some thing wrong.

First i Created a Column as per below

IF(Len([ASDPID])> '0', [ST ID],'') as CHKSTID,

so it will check what all STID we have ASDPID not equal to blank. and then i used below condition so it will Check if that CHKSTID available and ASDPID Blank then it will be 0 else 1

If(Exists([CHKSTID]) and [ASDPID] = ( IsNull([ASDPID]) or Len([ASDPID]) = '0') , '0' ,'1') as ASDPFlagNew,

but no luck .

According to me For particular STID , ASDPID is available at least once then it should flag as 0.

in above snapshot i just filtered to one STID where STID has 2 rows one with ASDPID available and other Blank but for me ASDPIDFlag should come as 0 for both Rows as ASDPID is available atleast once per that STID. else it should come as 1

Best Regards,

Re: Trying to create a Flag in QLikview

dunnalahk — Fri, 16 Jun 2017 15:53:49 GMT

Hi Ruben,

i used Apply Map . All your Inputs help me a lot thank you so Much.

Best Regards,

Re: Trying to create a Flag in QLikview

rubenmarin — Fri, 16 Jun 2017 15:55:33 GMT

but are you creating CHKSTID in the same table? I think that won't work because it will be evaluated row by row and we need something that evaluates between different records.

If CHKSTID is in a different table maybe you need to check as:

If(Exists('CHKSTID', [ST ID]), '0' ,'1') as ASDPFlagNew,

Re: Trying to create a Flag in QLikview

dunnalahk — Fri, 16 Jun 2017 15:57:10 GMT

Hi Rubin,

It solved thank you so Much.

Best Regards,