Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik1_User1
Specialist
Specialist

Please help , Backend logic to create flag, its urgent please

Hi All
please help with backend logic, its URgent please.

Load * inline [
UID, ACCESS, com, loc, div, id
A,USER,C1,L1,D1,668ea29fae73259dc3f26b93
A,USER,C1,L1,D1,66cd8412615a9782e6d7bb33
A,USER,C1,L1,D1,ALL
A,USER,C1,L1,D1,670505e9f26822d8733eccaf
A,USER,C1,L1,D1,668ea2b47bba6d1bad7ad89c
A,USER,C2,L1,D1,ALL
A,USER,C2,L1,D1,ALL
A,USER,C2,L1,D1,ALL
A,USER,C2,L1,D1,ALL
A,USER,C3,L1,D1,668ea2b47bba6d1bad7ad89c
A,USER,C3,L1,D1,670505e9f26822d8733eccaf
];

UID A and com C1 has ALL as well as other values  as well so mark as 1
UID A and com C2 has just "ALL" as values in id field so mark as 0
UID A and com C3 has just non ALL as values in id field so mark as 1

Expected output

User com flag
A C1 1
A C2 0
A C3 1
Labels (3)
2 Replies
Lisa_P
Employee
Employee

Here is the script I used with your data set:

Data:
Load * inline [
UID, ACCESS, com, loc, div, id
A,USER,C1,L1,D1,668ea29fae73259dc3f26b93
A,USER,C1,L1,D1,66cd8412615a9782e6d7bb33
A,USER,C1,L1,D1,ALL
A,USER,C1,L1,D1,670505e9f26822d8733eccaf
A,USER,C1,L1,D1,668ea2b47bba6d1bad7ad89c
A,USER,C2,L1,D1,ALL
A,USER,C2,L1,D1,ALL
A,USER,C2,L1,D1,ALL
A,USER,C2,L1,D1,ALL
A,USER,C3,L1,D1,668ea2b47bba6d1bad7ad89c
A,USER,C3,L1,D1,670505e9f26822d8733eccaf
];
 
Left Join(Data)
Load UID, 
com, 
    IF(FindOneOf(Concat, '6')>0,1, 0) as flag;
Load UID,
com,
    Concat(id) as Concat
Resident Data
Group By UID, com;
 
Here is the result on the sheet:
Lisa_P_0-1729118815480.png

 

The assumption that I made was that  there was a 6 in every id not containing ALL.

Kushal_Chawda

@Qlik1_User1  one more option is using window functions

Data:
Load *,
     if(Window(Count(com),UID,com, id='ALL') = Window(Count(com),UID,com),0,1) as flag
inline [
UID, ACCESS, com, loc, div, id
A,USER,C1,L1,D1,668ea29fae73259dc3f26b93
A,USER,C1,L1,D1,66cd8412615a9782e6d7bb33
A,USER,C1,L1,D1,ALL
A,USER,C1,L1,D1,670505e9f26822d8733eccaf
A,USER,C1,L1,D1,668ea2b47bba6d1bad7ad89c
A,USER,C2,L1,D1,ALL
A,USER,C2,L1,D1,ALL
A,USER,C2,L1,D1,ALL
A,USER,C2,L1,D1,ALL
A,USER,C3,L1,D1,668ea2b47bba6d1bad7ad89c
A,USER,C3,L1,D1,670505e9f26822d8733eccaf
];

 

Kushal_Chawda_0-1729122445200.png