topic Re: How to create an adjacent nodes table from an expanded nodes file? in QlikView

How to create an adjacent nodes table from an expanded nodes file?

Fri, 23 Dec 2011 16:20:10 GMT

I have a file in this format

Level1	Level2	Level3	Level4
1	1	1	1
1	1	1	2
1	2	2	3
1	2	2	4
1	2	3	4

and want to convert it to this format

Id	ParentId
1	1
2	1
3	1
4	1

anyone know of an easy way to do this?

How to create an adjacent nodes table from an expanded nodes file?

johnw — Wed, 28 Dec 2011 01:09:00 GMT

I don't understand your data. Why does the same number appear on multiple levels on the same line? Why do all IDs have the same parent?

Re: How to create an adjacent nodes table from an expanded nodes file?

Wed, 28 Dec 2011 01:49:04 GMT

Do you mean you want to do this:

ID	ParentID
1	1
1	1
1	1
1	1
1	1
2	1
2	1
2	2
2	2
2	2
3	1
3	1
3	2
3	2
3	3
4	1
4	2
4	3
4	3
4	4

Re: How to create an adjacent nodes table from an expanded nodes file?

Wed, 28 Dec 2011 01:50:16 GMT

It's just random data. Ill post an example of the real info.

On Dec 27, 2011, at 7:09 PM, John Witherspoon <

Re: How to create an adjacent nodes table from an expanded nodes file?

Wed, 28 Dec 2011 01:51:28 GMT

Yes, that's the format I need to end up with.

Sent from my iPad

On Dec 27, 2011, at 7:49 PM, "B.Sarcich" <

Re: How to create an adjacent nodes table from an expanded nodes file?

Wed, 28 Dec 2011 04:06:31 GMT

You could do something like this:

RAW:
LOAD * INLINE [
Level1, Level2, Level3, Level4
1, 1, 1, 1
1, 1, 1, 2
1, 2, 2, 3
1, 2, 2, 4
1, 2, 3, 4
];
FOR EACH FieldNo IN '1', '2', '3', '4'
LET FieldName = 'Level' & '$(FieldNo)';
ID_LIST:
LOAD
$(FieldNo) As ID,
$(FieldName) As ParentID
RESIDENT RAW;
NEXT FieldNo

RAW can be loaded from an excel file or wherever else. I am assuming here that the field names are Level1, Level2 etc. This example is of course not dynamic; you must know how many fields there are and code that into the FOR EACH section. Possibly you can come up with a way to dynamically put that into a variable and put the variable after the IN.

Someone else can probably come up with a smarter way to do this than me anyway.