topic Re: Script Level Flag in QlikView

Script Level Flag

brijesh1991 — Wed, 20 Feb 2013 07:25:33 GMT

Hi,

As shown in attached image,

If for a particular AppNo. if all ExDatesare there corresponding to All Codes then flag should be 'Full', if for a particular AppNo, if few Exdates are null and few are not null corresponding to all Code, then flag should be 'Partial' and if for a particaular AppNo, all ExDates are null corresponding to all codes, then flag should be 'None.

Example:In attached image, for AppNo.12, all ExDates are existed for all Codes, so flag should be 'Full' for App No.12

for AppNo.14 few ExDates are not existed for all Codes, so flag should be 'Partial' for App No.14

for AppNo.13 all ExDates are not existed for all Codes, so flag should be "None" for App No.13

I wanted to do this in my script level

My QVD has this three fields: AppNo, Code, ExDate

Your ideaes will be warm welcomed

Thanks and regards,

Brijesh

Re: Script Level Flag

Anonymous — Wed, 20 Feb 2013 07:50:54 GMT

If you want to do this in the script then you will need to relaod the table with this data in, with a group by clause in it that groups by AppNo, and then assign a new field that tests to see if count(ExDate) is the same as another count of another field that will always exist, AppNo for example.

Something like this:

LEFT JOIN (HostTable)

LOAD

AppNo,

if(count(ExDate)=count(AppNo),'Full',if(count(ExDate)=0,'None','Partial') AS CompletionFlag

RESIDENT HostTable

GROUP BY AppNo;

Be careful to test the size of HostTable before and after this to make sure its number of rows does not increase.

Jonathan

Re: Script Level Flag

brijesh1991 — Wed, 20 Feb 2013 08:13:45 GMT

Thanks jonbroughavone, but it gives me only 'Full' flag, not 'Partial' or 'None', because it checks the first condition, if it satisfies then it exits.

I have millions of AppNo, I have to check for all AppNo

Re: Script Level Flag

brijesh1991 — Wed, 20 Feb 2013 08:13:58 GMT

Thanks jonbroughavone, but it gives me only 'Full' flag, not 'Partial' or 'None', because it checks the first condition, if it satisfies then it exits.

I have millions of AppNo, I have to check for all AppNo

Re: Script Level Flag

brijesh1991 — Wed, 20 Feb 2013 08:22:01 GMT

Thanks jonbroughavone, but it gives me only 'Full' flag, not 'Partial' or 'None', because it checks the first condition, if it satisfies then it exits.

I have millions of AppNo, I have to check for all AppNo

Re: Script Level Flag

brijesh1991 — Wed, 20 Feb 2013 08:53:31 GMT

Thanks jonbroughavone, but it gives me only 'Full' flag, not 'Partial' or 'None', because it checks the first condition, if it satisfies then it exits.

I have millions of AppNo, I have to check for all AppNo

Re: Script Level Flag

jonathandienst — Wed, 20 Feb 2013 09:15:19 GMT

This script fragment works

Join (Data)

LOAD

AppNo,

If(Count(If(Len(ExDate) > 0, AppNo)) = Count(AppNo), 'Full',

If(Count(If(Len(ExDate) > 0, AppNo)) > 0, 'Partial', 'None')) As State

Resident Data

Group By AppNo;

See attached file.

Regards

Jonathan

Edit:

PS tested Jonathan B's script and it works for me (it is not materially different from the above)

Re: Script Level Flag

brijesh1991 — Wed, 20 Feb 2013 09:27:56 GMT

It's working...

Thanks jonbroughavone