QlikSense Script

[jorditorras]

Dear Gurus, 

I've got an scenario where each record (Node_ID) has an assigned Level of Hierarchy (Level 1 to Level 5) and in some cases the users plan the hours at the last level and in some other cases they plan the hours at higher Levels and lower levels at the same time (as in the following example).

I also have a field that concatenates all the levels of each node. So, what I'm trying to get is to prioritize the lower levels by following a rule as:

If one Node_ID has "Hours" planned but there are some other records in the table with a lower level with "Hours" planned, then the "Hours" of this first record needs to be transformed in a 0. 

So, following the previous example, I'd like to get the table as:

Any idea about how can I manage this in the script??

Many thanks in advance!!

[edwin]

i would reword the ask to simplify it and explain my proposed solution.  if you are able to associate a level number with each node and count how many distinct levels each node has then you can identify nodes with multiple levels

therefore the problem is to find nodes with multiple levels (see my note later on).

the first task is to determine the level of the node:

data:
load *,
if(len(L5)>0, 5,
            if(len(L4)>0, 4,
            if(len(L3)>0, 3,
            if(len(L2)>0, 2,
                        1
)))) as level;
load * inline [
NodeID, L1, L2, L3, L4, L5, Hrs
A, a for L1, a for L2,,,,5
A, a for L1, a for L2,a for L3,,,7
B, b for L1, b for L2,,,,50
B, 2nd b for L1, 2nd b for L2,,,,20
C, C for L1,,,,,5
];

NoConcatenate
counts:
load NodeID, if(count(distinct level)>1,'Yes') as HasLowerLevel
Resident data
group by NodeID;

 in your expression you can build in the business rule that if HasLowerLevel='Yes' and Hrs>0 dont display

the reason i didnt build this rules into the script is to make the data model flexible.  but you can of course build that in to make the presentation run faster.

note, in your description you said look for nodes with lower levels.  your example shows a different business rule.  the fourth node actually has no lower level but has multiples of the same level.  maybe this was just an oversight or you have the same node with a lower or higher level.  

if the business rule were to find multiples, then you just do a count of the level not a distinct 

in any case the solution is pretty much the same just a slight tweak.

hope that helps

[vinieme12]

What happens if there are more than 2  level5 nodes for a particular level4

for example, what should the output be for below?

Lvl4, lvl5,hours

laminacion Alella,  null, 6

laminacion Alella,  almacen mas colell, 5

laminacion Alella, Something else, 0

laminacion Alella, Something other thing, 8

[jorditorras]

Hi Vinieme12, 

If there are more than 2 level5 nodes for a particular level 4, the result of the Node_ID of level 4 should turn to 0 in case the levels 5 have some results. So, the output of what you expose should be:

Lvl4, lvl5,hours

laminacion Alella,  null, 0

laminacion Alella,  almacen mas colell, 5

laminacion Alella, Something else, 0

laminacion Alella, Something other thing, 8

Thanks, 

Regards, 

Jordi

[jorditorras]

Hi Edwin, 

Thanks, your post is helping me a lot and I'm focusing my solution on what you posted. 

But I'd like to clarify one thing and post one question...

About what you said that 4th Node has no lower level, according my interpretation it does. The 5th Node is a lower lever as it was the same "Level 4" and a lower "Level 5". That's why this "Level 5" is the one that should be relevant and the "Level 4" should turn to 0.

So, my questions are:

- Whats the Purpose of this specific Load INLINE that you posted? Or it is just a representation of my main table?

- Why the "if(count(dictinct level)>1,'Yes')" would define the Haslowerlevel? It would be possible that there are two Nodes_ID with different (for example) "Level 3" but it doesn't mean that there's some other Nodes_ID with a lower Level...

Thank you very much for your help!!!

Regards, 

Jordi