Trying to create a Flag in QLikview

[dunnalahk123]

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,

HK

[rubenmarin]

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.

[dunnalahk]

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,

[dunnalahk]

Hi Ruben,

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

Best Regards,

Hk

[rubenmarin]

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,

[dunnalahk]

Hi Rubin,

It solved thank you so Much.

Best Regards,