How can I use dynamic variables during load?

[Dan17]

Hello Community,

another of my Parent Child problems. I have a big data table file and want to split it into multiple smaller tables that contain a reference to their parent (table). The data that is being parsed can be reduced to this:

ID Type
1   Parent
2   Child
3   Parent
4   Child
5   Child
6   Child

The result should be

ParentID ChildID
1                 2
3                 4
3                 5
3                 6

I tried to use a variable in the load statement that changes its value during the load, but the ParentID is always Null. 

Let vParent = Null();

MyTable:
LOAD
If([Type]='Parent', '$(vParent)'=[ID],'$(vParent)') as ParentID,
If([Type]='Child', [ID], Peek('ChildID')) as ChildID,
FROM [lib://AttachedFiles/whatever.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);

What did I do wrong?

[RafaelBarrios]

Hi @Dan17 

Have you tried generic load?

I think that should do the trick

https://community.qlik.com/t5/Design/The-Generic-Load/ba-p/1473470

hope this helps.

best,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂

[Dan17]

Hi @RAF 

the answer is no. Until now I didn't even know something like generic load existed. After going thru the article and looking at my real source table, generic load would do more harm than good.  

I'm currently working on splitting my input table into several smaller tables that contain the parent/child information before I let Qlik sense load it. A workaround but better than getting frustrated over another thing that Qlik sense is not able to do.