Run a number of iterations on field in load script to return value

[Qlik_ULG]

I have 2 basic database tables which are linked based on Customer ID.

The first table (Customer) contains the CustomerID and CustomerName. The second (CustomerDetail) contains CustomerID and other attributes, including ParentID.

The ParentID is simply the CustomerID for the Parent Customer of the original. Linking this back to the Customer table,  CustomerName returns the Parent Customer Name.

However, there will often be a number of iterations, where the Parent Customer will have its own ParentID and subsequent Parent Customer, etc.

I'm trying create a condition in my load script that only returns the final ParentID value, so I can return the Master Parent Customer. I.e. ParentID which when loaded as a CustomerID has no further ParentID.

E.g The tables are structured as follows:

Customer:

CustomerID	CustomerName
1	Red
2	Black
3	Grey
4	Green
5	Yellow
6	Orange
7	Blue
8	Purple
9	White
10	Silver
11	Purple

CustomerDetail:

CustomerID	ParentID
1	5
2
3	7
4	1
5	2
6	1
7	11
8	2
9	3
10	3
11

My load script is currently returning only the first Parent Client value for each (and the value itself if ParentID is null):

CustomerID	CustomerName	ParentID	Parent Client
1	Red	5	Yellow
2	Black	2	Black
3	Grey	7	Blue
4	Green	1	Red
5	Yellow	2	Black
6	Orange	1	Red
7	Blue	11	Purple
8	Purple	2	Black
9	White	3	Grey
10	Silver	3	Grey
11	Purple	11	Purple

I'm trying to get it run a number of iterations so that it returns the final value:

CustomerID	CustomerName	ParentID	Parent Client
1	Red	2	Black
2	Black	2	Black
3	Grey	11	Purple
4	Green	2	Black
5	Yellow	2	Black
6	Orange	2	Black
7	Blue	11	Purple
8	Purple	2	Black
9	White	11	Purple
10	Silver	11	Purple
11	Purple	11	Purple

I have tried a number of varying IF statements in the load script, but have been unable to get the required result.

Thank you for any help you can provide.

[sunny_talwar · Accepted solution]

Try this

CusDetails:
LOAD CustomerID,
	 ParentID as ParentID1;
LOAD * INLINE [
    CustomerID, ParentID
    1, 5
    2,  
    3, 7
    4, 1
    5, 2
    6, 1
    7, 11
    8, 2
    9, 3
    10, 3
    11,  
];

Left Join (CusDetails)
LOAD CustomerID as [ParentID1],
	 [ParentID1] as [ParentID2]
Resident CusDetails;

FOR i = 2 to 10

	LET j = $(i) - 1;
	LET k = $(i) + 1;

	Left Join (CusDetails)
	LOAD [ParentID$(j)] as [ParentID$(i)],
		 [ParentID$(i)] as [ParentID$(k)]
	Resident CusDetails;
	
NEXT

TempCusDetails:
CrossTable(Parent, Value)
LOAD CustomerID as CustomerMain,
	 *
Resident CusDetails;

DROP Table CusDetails;

Temp2CusDetails:
NoConcatenate
LOAD CustomerMain as CustomerID,
	 Parent,
	 Value as ParentID
Resident TempCusDetails
Where Parent <> 'CustomerID'
Order By CustomerMain, Parent desc;

Left Join (Temp2CusDetails)
LOAD CustomerID,
	 Count(DISTINCT Parent) as Count
Resident Temp2CusDetails
Group By CustomerID;

FinalCusDetails:
LOAD DISTINCT CustomerID,
	 If(Count = 1, CustomerID, ParentID) as ParentID
Resident Temp2CusDetails
Where Len(Trim(If(Count = 1, CustomerID, ParentID))) > 0 and If(Count > 1, CustomerID <> Peek('CustomerID'), -1);

DROP Table TempCusDetails, Temp2CusDetails;

Left Join (FinalCusDetails)
LOAD * INLINE [
    CustomerID, CustomerName
    1, Red
    2, Black
    3, Grey
    4, Green
    5, Yellow
    6, Orange
    7, Blue
    8, Purple
    9, White
    10, Silver
    11, Purple
];

Left Join (FinalCusDetails)
LOAD * INLINE [
    ParentID, Parent Client
    1, Red
    2, Black
    3, Grey
    4, Green
    5, Yellow
    6, Orange
    7, Blue
    8, Purple
    9, White
    10, Silver
    11, Purple
];

[Qlik_ULG]

Thank you very much, Sunny.

This does provide the end client. Given the size of my dataset, it does result in a much increased load time, but I will have it generated separately.

Thanks again.