Create a Flag in Script based on Condition

[qlikwiz123]

I have data in the following way

ID        CatID      Status               Flag   (Expected Field)

1           1A          Pending            Complete/Pending

1            1B         Complete         Complete/Pending

2            2A          Complete         Complete

3            3A          Pending             Pending

3            3B           Pending            Pending

3           3C            Pending             Pending

4            4A            Complete          Complete

5             5A           Pending          Complete/Pending

5              5B           Complete      Complete/Pending

I need to create a new field called 'Flag' that says 'Complete/Pending' for the IDs that has both 'Pending' and 'Complete' in Status. If the ID has only one value in the Status field, then show the same Status value in the 'Flag'

How do I do this in the script?

[Marcos_rv · Accepted solution]

Try this :

TABLE1:

LOAD ID, 

CatID,

Status

from XXXXX.QVD;

LEFT JOIN (TABLE1)

load ID,

Concat(distinct Status ,'-' ) as Flag

resident TABLE1;

Saludos!!!

[qlikwiz123]

Says Invalid Expression

[qlikwiz123]

Needed to Group By. Will try. Thanks

[Anil_Babu_Samineni]

PFA

[Marcos_rv]

yep, 

group by ID;

Very close !! jajaja