Script Logic

[tmumaw]

Good morning everyone,

Need a little logic help.  I have a user QVD which I use for section access.  The user file has Nodes 1 - 9 in it but only 1 of the nodes will be populated.  I have another qvd which has the Nodes in it with Nodes 1 - 9 populated.  I need to fill in the blanks.  For example:

USERID  Node1   Node2  Node3  Node4  Node5  Node6  Node7  Node8  Node9  SAPPROFITCENTER

JOE                                                                               N5

BOB                                                                                                                                                            134331

NODE QVD

Node1    Node2    Node3   Node4   Node5   Node6   Node7   Node8   Node9   SAPPROFITCENTER

N1                N2           N3           N4            N5           N6          N7           N8           N9                132331

So for Joe I need to populate N1 - N4  and for Bob do nothing.

Here is the script I am using.  Any help would be appreciated....sort of under the gun.  Thanks

SAUsers:
LOAD ACCESS,
USERID,
NODE1,
NODE2,
NODE3,
NODE4,
NODE5,
NODE6,
NODE7,
NODE8,
NODE9,
SAPROFITCENTER
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd);

CoHier:
LOAD
Upper(Node1) as NODE1,
Upper(Node2) as NODE2,
Upper(Node3) as NODE3,
Upper(Node4) as NODE4,
Upper(Node5) as NODE5,
Upper(Node6) as NODE6,
Upper(Node7) as NODE7,
Upper(Node8) as NODE8,
Upper(Node9) as NODE9,
ParentGroup,
Path,
NodeIDCostCenterName,
ParentIDGroup,
Node
FROM
[D:\Qlikview\QVD\Section Access\ExpandedNodes.QVD]
(qvd);

Inner Join (CoHier)
LOAD NodeIDCostCenterName,
[Profit Center],
SAPROFITCENTER,
[Profit Center Name],
[PC Desc]
FROM
[D:\Qlikview\QVD\Section Access\CostCenterGroupHierarchy.QVD]
(qvd);

[Martijn_W]

Good evening,

So if I understand correctly, you need to populate all the lower nodes to the one that is populated in the SAUsers table, correct?

One solution would be to use a series of IF and OR statements to evaluate the nodes above for an entry for each field, something like this:

SAUsers:
LOAD ACCESS,
USERID,
if(LEN(NODE9)>0 OR LEN(NODE8)>0 OR LEN(NODE7)>0 OR LEN(NODE6)>0  OR LEN(NODE5)>0 OR LEN(NODE4)>0 OR LEN(NODE3)>0 OR LEN(NODE2)>0 ,'N1',  NODE1) AS NODE1,
if(LEN(NODE9)>0 OR LEN(NODE8)>0 OR LEN(NODE7)>0 OR LEN(NODE6)>0  OR LEN(NODE5)>0 OR LEN(NODE4)>0 OR LEN(NODE3)>0,'N2', NODE2) AS NODE2,
if(LEN(NODE9)>0 OR LEN(NODE8)>0 OR LEN(NODE7)>0 OR LEN(NODE6)>0  OR LEN(NODE5)>0 OR LEN(NODE4)>0 ,'N3', NODE3) AS NODE3,
if(LEN(NODE9)>0 OR LEN(NODE8)>0 OR LEN(NODE7)>0 OR LEN(NODE6)>0  OR LEN(NODE5)>0 ,'N4', NODE4) AS NODE4,
if(LEN(NODE9)>0 OR LEN(NODE8)>0 OR LEN(NODE7)>0 OR LEN(NODE6)>0 ,'N5',NODE5) AS NODE5,
if(LEN(NODE9)>0 OR LEN(NODE8)>0 OR LEN(NODE7)>0 ,'N6', NODE6) AS NODE6,
if(LEN(NODE9)>0 OR LEN(NODE8)>0 ,'N7', NODE7) AS NODE7,
if(LEN(NODE9)>0,'N8', NODE8) AS NODE8,
NODE9,
SAPROFITCENTER
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd);

For each field, evaluate in order, with a set of OR arguments, if any of the above fields are populated with a value ( LEN( field ) >0 ). If any of them are, return the relevant N1-9 value. If none of them are, return the value of the field itself. It is not the most elegant solution, but it will do the trick in a pinch.

I hope this helps you for now, it's no fun to be under the gun. Good luck!

If the node number are not N1-9 but other numbers that are not static, use an applymap after populating the lower nodes.

Martijn

PS. If this does not accomplish what you need, could you give an example of the you real data, with alterations in them to anonymize it?

[Saravanan_Desingh]

Try this,

tab1:
LOAD * INLINE [
    USERID, Node1, Node2, Node3, Node4, Node5, Node6, Node7, Node8, Node9, SAPPROFITCENTER
    Joe, , , , , N5, , , , , 
    Bob, , , , , , , , , , 134331
    Sam, , , ,N4, , , , , , 134331
];

Left Join(tab1)
LOAD USERID, Pick(Match(-1,Len(Node1)>0,Len(Node2)>0,Len(Node3)>0,Len(Node4)>0,Len(Node5)>0,
Len(Node6)>0,Len(Node7)>0,Len(Node8)>0,Len(Node9)>0),1,2,3,4,5,6,7,8,9) As HighNode
Resident tab1;

Left Join(tab1)
LOAD USERID, IterNo() As Key
Resident tab1
While IterNo()<=HighNode;

M1:
LOAD * INLINE [
    Node1, Node2, Node3, Node4, Node5, Node6, Node7, Node8, Node9
    N1, N2, N3, N4, N5, N6, N7, N8, N9
];

M1X:
CrossTable(Node,mValue)
LOAD 1, * Resident M1;

map1:
Mapping
LOAD * INLINE [
    Node, Key
    Node1, 1
    Node2, 2
    Node3, 3
    Node4, 4
    Node5, 5
    Node6, 6
    Node7, 7
    Node8, 8
    Node9, 9
];

tab2:
LOAD Distinct USERID
Resident tab1;

Left Join(tab2)
LOAD ApplyMap('map1',Node) As Key, Node, mValue Resident M1X;

Left Join(tab1)
LOAD * Resident tab2;

tab3:
Generic
LOAD USERID, SAPPROFITCENTER, Node, mValue
Resident tab1;

Drop Table M1,  M1X,  tab1, tab2;

[Saravanan_Desingh]

Output:

[tmumaw]

Thank you.  I think we are very close.  I have attached 2 spreadsheets one from your output and one which is called ActualUsers which is my input file.  I have attached the same user from both files.  When you look at my file Node6X is populated with a value.  I need to populate Node1X - Node5X with the values.  The one with Node5X needs Node1X - Node4X populated.  We don't need to do anything for the ones with SapPC only, but they need to stay in the file.  I think your solution is so close.  I really appreciate all your help.  Being under the gun to get this to work is tough.

[Saravanan_Desingh]

Did u try like this? I have not tested this though.

tab1:
LOAD * INLINE [
    UseridX, Node1X, Node2X, Node3X, Node4X, Node5X, Node6X, Node7X, Node8X, Node9X, SapPCX
    Joe, , , , , N5, , , , , 
    Bob, , , , , , , , , , 134331
    Sam, , , ,N4, , , , , , 134331
];

Left Join(tab1)
LOAD UseridX, Pick(Match(-1,Len(Node1X)>0,Len(Node2X)>0,Len(Node3X)>0,Len(Node4X)>0,Len(Node5X)>0,
Len(Node6X)>0,Len(Node7X)>0,Len(Node8X)>0,Len(Node9X)>0),1,2,3,4,5,6,7,8,9) As HighNode
Resident tab1;

Left Join(tab1)
LOAD UseridX, IterNo() As Key
Resident tab1
While IterNo()<HighNode;

M1:
LOAD * INLINE [
    Node1X, Node2X, Node3X, Node4X, Node5X, Node6X, Node7X, Node8X, Node9X
    N1, N2, N3, N4, N5, N6, N7, N8, N9
];

M1X:
CrossTable(Node,mValue)
LOAD 1, * Resident M1;

map1:
Mapping
LOAD * INLINE [
    Node, Key
    Node1X, 1
    Node2X, 2
    Node3X, 3
    Node4X, 4
    Node5X, 5
    Node6X, 6
    Node7X, 7
    Node8X, 8
    Node9X, 9
];

tab2:
LOAD Distinct UseridX
Resident tab1;

Left Join(tab2)
LOAD ApplyMap('map1',Node) As Key, Node, mValue Resident M1X;

Left Join(tab1)
LOAD * Resident tab2;

tab3:
Generic
LOAD UseridX, SapPCX, Node, mValue
Resident tab1;

Drop Table M1,  M1X,  tab1, tab2;

[tmumaw · Accepted solution]

Thank you for all of your help.  I ended up going a different direction.  Here's what I did.

Expandednodes:
LOAD Upper(Node1) as NODE1,
Upper(Node2) as NODE2,
Upper(Node3) as NODE3,
Upper(Node4) as NODE4,
Upper(Node5) as NODE5,
Upper(Node6) as NODE6,
Upper(Node7) as NODE7,
Upper(Node8) as NODE8,
Upper(Node9) as NODE9
FROM
[D:\Qlikview\QVD\Section Access\ExpandedNodes.QVD]
(qvd);

Node2_Out:
NoConcatenate
Load
NODE1,
NODE2
Resident Expandednodes
Where (not IsNull(NODE2)) and IsNull(NODE3)
;

Inner Join (Node2_Out)
LOAD ACCESS,
USERID,
SAPROFITCENTER,
NODE2
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd)
Where (not IsNull(NODE2)) //and IsNull(NODE3)
;

Node3_Out:
NoConcatenate
Load NODE1,
NODE2,
NODE3
Resident Expandednodes
Where (not IsNull(NODE3)) and IsNull(NODE4)
;

Inner Join (Node3_Out)
LOAD ACCESS,
USERID,
SAPROFITCENTER,
NODE3
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd)
Where (not IsNull(NODE3)) //and IsNull(NODE4)
;

Node4_Out:
NoConcatenate
Load NODE1,
NODE2,
NODE3,
NODE4
Resident Expandednodes
Where (not IsNull(NODE4)) and IsNull(NODE5)
;

Inner Join (Node4_Out)
LOAD ACCESS,
USERID,
SAPROFITCENTER,
NODE4
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd)
Where (not IsNull(NODE4)) //and IsNull(NODE4)
;

Node5_Out:
NoConcatenate
Load NODE1,
NODE2,
NODE3,
NODE4,
NODE5
Resident Expandednodes
Where (not IsNull(NODE5)) and IsNull(NODE6)
;

Inner Join (Node5_Out)
LOAD ACCESS,
USERID,
SAPROFITCENTER,
NODE5
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd)
Where (not IsNull(NODE5)) //and IsNull(NODE4)
;

Node6_Out:
NoConcatenate
Load NODE1,
NODE2,
NODE3,
NODE4,
NODE5,
NODE6
Resident Expandednodes
Where (not IsNull(NODE6)) and IsNull(NODE7)
;

Inner Join (Node6_Out)
LOAD ACCESS,
USERID,
SAPROFITCENTER,
NODE6
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd)
Where (not IsNull(NODE6)) //and IsNull(NODE4)
;

Node7_Out:
NoConcatenate
Load NODE1,
NODE2,
NODE3,
NODE4,
NODE5,
NODE6,
NODE7
Resident Expandednodes
Where (not IsNull(NODE7)) and IsNull(NODE8)
;

Inner Join (Node7_Out)
LOAD ACCESS,
USERID,
SAPROFITCENTER,
NODE7
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd)
Where (not IsNull(NODE7)) //and IsNull(NODE4)
;

Node8_Out:
NoConcatenate
Load NODE1,
NODE2,
NODE3,
NODE4,
NODE5,
NODE6,
NODE7,
NODE8
Resident Expandednodes
Where (not IsNull(NODE8)) and IsNull(NODE9)
;

Inner Join (Node8_Out)
LOAD ACCESS,
USERID,
SAPROFITCENTER,
NODE8
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd)
Where (not IsNull(NODE8)) //and IsNull(NODE4)
;

Node9_Out:
NoConcatenate
Load NODE1,
NODE2,
NODE3,
NODE4,
NODE5,
NODE6,
NODE7,
NODE8,
NODE9
Resident Expandednodes
Where (not IsNull(NODE9))
;

Inner Join (Node9_Out)
LOAD ACCESS,
USERID,
SAPROFITCENTER,
NODE9
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd)
Where (not IsNull(NODE9))
;

NoConcatenate
PC:
LOAD ACCESS,
USERID,
SAPROFITCENTER,
NODE1,
NODE2,
NODE3,
NODE4,
NODE5,
NODE6,
NODE7,
NODE8,
NODE9
FROM
[D:\Qlikview\QVD\Section Access\SAUsers.QVD]
(qvd)
Where Len(SAPROFITCENTER) > 0;

NoConcatenate
Users:
Load *
Resident Node2_Out
;
Concatenate
Load *
Resident Node3_Out
;
Concatenate
Load *
Resident Node4_Out
;
Concatenate
Load *
Resident Node5_Out
;
Concatenate
Load *
Resident Node6_Out
;
Concatenate
Load *
Resident Node7_Out
;
Concatenate
Load *
Resident Node8_Out
;
Concatenate
Load *
Resident Node9_Out
;
Concatenate
Load *
Resident PC
;
Drop table Node2_Out, Node3_Out, Node4_Out, Node5_Out, Node6_Out, Node7_Out, Node8_Out, Node9_Out, PC
;
Drop table Expandednodes
;

Exit SCRIPT;