Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_ULG
Creator
Creator

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

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:

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

 

CustomerDetail:

CustomerIDParentID
15
2 
37
41
52
61
711
82
93
103
11 

 

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

CustomerIDCustomerNameParentIDParent Client
1Red5Yellow
2Black2Black
3Grey7Blue
4Green1Red
5Yellow2Black
6Orange1Red
7Blue11Purple
8Purple2Black
9White3Grey
10Silver3Grey
11Purple11Purple

 

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

CustomerIDCustomerNameParentIDParent Client
1Red2Black
2Black2Black
3Grey11Purple
4Green2Black
5Yellow2Black
6Orange2Black
7Blue11Purple
8Purple2Black
9White11Purple
10Silver11Purple
11Purple11Purple

 

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.

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

2 Replies
sunny_talwar

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

View solution in original post

Qlik_ULG
Creator
Creator
Author

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.