Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brijesh1991
Partner - Specialist
Partner - Specialist

Script Level Flag

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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

View solution in original post

7 Replies
Anonymous
Not applicable

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

brijesh1991
Partner - Specialist
Partner - Specialist
Author

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

brijesh1991
Partner - Specialist
Partner - Specialist
Author

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

brijesh1991
Partner - Specialist
Partner - Specialist
Author

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

brijesh1991
Partner - Specialist
Partner - Specialist
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
brijesh1991
Partner - Specialist
Partner - Specialist
Author

It's working...

Thanks jonbroughavone