Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
draghici1109
Creator
Creator

Generic Load with a fix number of columns pivoted

Hello community,

I have the chalenge to write a Qlik Script that basically does a GENERIC LOAD.

For the sake of understanding, let describe the sample data:

For a family, we have for each child name and birthdate. So the records look like:

Key | Parent Name | Child No | Child Name | Birthdate

1 | Martin Smith | 1 | Adam Smith | 04/12/1990

2 | Martin Smith | 2 | Abel Smith | 07/07/1993

3 | Martin Smith |  3 | Aaron Smith | 10/01/1996

4 | Barbara Black | 1 | David Black | 10/03/1999

5 | Barbara Black | 2 | Mary Black | 05/07/2001

6 | Barbara Black | 3 |Miriam Black | 10/04/2003

7 | Barbara Black | 4 |Adrian Black | 07/01/2005

With the generic load I want to get one record with the structure

|Parent Name | Child1| Child1 Name | Child1 Birthday | | Child2| Child2 Name | Child2 Birthday |  Child3| Child3 Name | Child3 Birthday | Child4| Child4 Name | Child4 Birthday |

Result of the GENERIC LOAD will be

| Martin Smith | 1 | Adam Smith | 04/12/1990 |  2 | Abel Smith | 07/07/1993 |  3 | Aaron Smith | 10/01/1996||||

| Barbara Black | 1 | David Black | 10/03/1999 |  2 | Mary Black | 05/07/2001 | 3 |Miriam Black | 10/04/2003 |  4 |Adrian Black | 07/01/2005

The chalenge is, that the number of the childer for a Parent may vary, from 1 to (theoretically dozens). However, the request ist, that I will deliver records with a maximum of 10 children. If there are for example 4 children, then the rest of the six children will be empty.

I applied the recomandation from this article https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470, yet it seems to me that it does not treat this special case I have to solve.

 

Alexandru Draghici
BICC at Komm.ONE
Labels (3)
2 Replies
chrismarlow
Specialist II
Specialist II

Hi,

I've never used generic load ... you could try something like this (assumes all the parent names are unique & they all have a Child No=1);

data:
Load * Inline [
Key | Parent Name | Child No | Child Name | Birthdate
1 | Martin Smith | 1 | Adam Smith | 04/12/1990
2 | Martin Smith | 2 | Abel Smith | 07/07/1993
3 | Martin Smith |  3 | Aaron Smith | 10/01/1996
4 | Barbara Black | 1 | David Black | 10/03/1999
5 | Barbara Black | 2 | Mary Black | 05/07/2001
6 | Barbara Black | 3 |Miriam Black | 10/04/2003
7 | Barbara Black | 4 |Adrian Black | 07/01/2005
] (delimiter is '|');

maxchildno:
Load	
	max([Child No]) as maxchildno
Resident data;

maxchildno=Peek('maxchildno');

DROP TABLE maxchildno;

finaldata:
Load
	[Parent Name],
	[Child No] AS Child1,
	[Child Name] AS [Child1 Name],
	[Birthdate] AS [Child1 Birthdate]
Resident data
WHERE [Child No]=1;

For i=2 to maxchildno;
	
	Left Join (finaldata)
	Load
		[Parent Name],
		[Child No] AS Child$(i),
		[Child Name] AS [Child$(i) Name],
		[Birthdate] AS [Child$(i) Birthdate]
	Resident data
	WHERE [Child No]=$(i);

Next i;

DROP TABLE data;

Cheers,

Chris.

marcus_sommer

I think you need to prepare your data before you transform them with a generic load. Maybe the following give you some ideas for it:

t1:
load
[Parent Name], [Child No], [Child Name], [Birthdate], [Parent Name] & '|' & [Child No] as ExistsKey
from X where [Child No] <= 10;

t2:
load
text(fieldvalue('Parent Name', recno())) as [Parent Name],
iterno() as [Child No], null() as [Child Name], null() as [Birthdate]
autogenerate fieldvaluecount('Parent Name') while iterno() <= 10;

concatenate(t3) load * resident t2 where not exists(ExistsKey, [Parent Name] & '|' & [Child No]);

drop fields ExistsKey; drop tables t2;