topic Re: Script Logic in QlikView

Script Logic

tmumaw — Fri, 12 Feb 2021 13:56:55 GMT

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);

Re: Script Logic

Martijn_W — Sat, 13 Feb 2021 00:31:18 GMT

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?

Re: Script Logic

Saravanan_Desingh — Sun, 14 Feb 2021 03:19:46 GMT

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;

Re: Script Logic

Saravanan_Desingh — Sun, 14 Feb 2021 03:20:15 GMT

Output:

Re: Script Logic

tmumaw — Mon, 15 Feb 2021 13:11:37 GMT

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.

Re: Script Logic

Saravanan_Desingh — Mon, 15 Feb 2021 17:13:38 GMT

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;

Re: Script Logic

tmumaw — Wed, 17 Feb 2021 14:31:30 GMT

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;