For loop in Qlik Sen

[w21450]

Hi everyone

I am somewhat new to Qlik Sense and have been working in it for just about 1/2 year and this is my first post here.

I am struggling right now with the creation of a for loop to solve an issue I have. I will try, as best I can, to give a full explanation of the problem:

I have created a dummy data set, to make it easier to explain what I am trying to do (please see the attached photo at the bottom of this post). 

So the logic I need to implement is: If any "Case 1" has any "Case 2" that has any "Case 3" that has a value = "white" then it should return the string "Challenge" in the "Category" column for ALL the "Case 2" under the same "Case 1". 

If "Case 3" = green then it has to return the string "Specialist" for in the category column for "Case 2" under the same specifik "Case 1". 

If none of those two colors are there then it should return "Normal".

I know this explanation is a bit messy, so I'll try to come with an example in regards to the screen shot. I want it to loop through alle the the different values in "Case 1". F.x. for all values = 1 in "Case 1" I want it to do another loop on alle the values in "Case 2" where "Case 1" = 1; (i.e. Case 2 = a,b,c and d). Then for these values I want it to look at every value in "Case 3" under Case 2 = (a,b,c and d) and check all these. If any of those values = "white" then I want it to stop and return "Challenge" i ALL cells referring to "Case 1" = 1 (i.e. row 2, 3, 4 and 5). If it any of the values in the category column is "green" then I want it to return "Specialist" in alle the cells referring to "Case 1" = 1. If none of these colors are in the list then it should return "Normal".

However, I want green to take precedence. So if any "Case 3" under "Case 1" = 1, is equal to "green" then the ALL cells in the category column where "Case 1" = 1, it should return "specialist" regardless - even if the color "white" is in one of the other cells under "Case 1" = 1. If the color white is present but no green is there then it should return "challenge". If none of the colors are present it should return "Normal".

I will try to write the for loop I am thinking will solve the problem. I know it will not have the correct syntax - it's mostly to give you an idea of how I think it could be solved:

For x in Case 1

        For y in Case 2

               IF Case 3 = green THEN

                      "Specialist" as Category where Case 1 = x

               ELIF Case 3 = white THEN 

                      "Challenge" as Category where Case 1 = x

                ELSE

                      "Normal" as Category where Case 1 = x

          NEXT Case 2

Next Case 1

One problem I see in this loop, however, is that here "Specialist" doesn't have precedence as I mentioned before that it should have.

I'm sorry that the explanation is so very messy! - but I hope some of you can help.

Have a nice day.

Best regards,

Peter

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD * INLINE [
    Case 1, Case 2, Case 3
    1, a, Red
    1, b, Red
    1, c, Blue
    1, d, White
    2, e, Blue
    2, f, Blue
    2, g, Red
    2, h, Red
    3, i, Black
    3, j, Purple
    4, k, Green
    4, l, Black
    4, m, White
    4, n, Blue
    4, o, Red
    5, p, Blue
    5, q, Black
    6, r, White
    6, s, Black
    6, t, Green
    7, u, Blue
    7, v, Purple
    7, w, Red
    8, x, Red
    8, y, White
];

Left Join(tab1)
LOAD *, If(Index(Str,'Green'), 'Specialist', If(Index(Str,'White'), 'Challenge', 'Normal')) As Category; 
LOAD [Case 1], Concat(DISTINCT [Case 3]) As Str
Resident tab1
Group By [Case 1];

Drop Field Str;

[Saravanan_Desingh · Accepted solution]

Output.

[w21450]

Thank you so much!!

That really solved my problem! 🙂

[Saravanan_Desingh]

you welcome