Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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
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)
It's working...
Thanks jonbroughavone