Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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;