Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey Guys!
I need help!
I have data in a text file, like this:
|REG1|INFO|INFO|INFO|
|REG2|INFO|INFO|INFO|INFO|INFO|
|REG2|INFO|INFO|INFO|INFO|INFO|
|REG2|INFO|INFO|INFO|INFO|INFO|
|REG1|INFO|INFO|INFO|
|REG2|INFO|INFO|INFO|INFO|INFO|
|REG2|INFO|INFO|INFO|INFO|INFO|
|REG2|INFO|INFO|INFO|INFO|INFO|
|REG2|INFO|INFO|INFO|INFO|INFO|
|REG2|INFO|INFO|INFO|INFO|INFO|
|REG1|INFO|INFO|INFO|
|REG2|INFO|INFO|INFO|INFO|INFO|
|REG2|INFO|INFO|INFO|INFO|INFO|
This go on by an N number of lines, can exceed a million rows.
The thing is, I will always have a |REG1| and N numbers of |REG2|, they are parent.
What I want too achieve in Qlik is something like this:
|1|REG1|INFO|INFO|INFO|
|1|REG2|INFO|INFO|INFO|INFO|INFO|
|1|REG2|INFO|INFO|INFO|INFO|INFO|
|1|REG2|INFO|INFO|INFO|INFO|INFO|
|2|REG1|INFO|INFO|INFO|
|2|REG2|INFO|INFO|INFO|INFO|INFO|
|2|REG2|INFO|INFO|INFO|INFO|INFO|
|2|REG2|INFO|INFO|INFO|INFO|INFO|
|2|REG2|INFO|INFO|INFO|INFO|INFO|
|2|REG2|INFO|INFO|INFO|INFO|INFO|
|3|REG1|INFO|INFO|INFO|
|3|REG2|INFO|INFO|INFO|INFO|INFO|
|3|REG2|INFO|INFO|INFO|INFO|INFO|
I been trying to do this all day and couldn't get it done, but seems to be so simple.
Can anyone help me?
Maybe this will script will help you:
LOAD if(B='REG1',alt(peek('Reg1New'),0)+1,peek('Reg1New')) as Reg1New, *
inline [
A|B|C|D|E|F
1|REG1|INFO|INFO|INFO|
2|REG2|INFO|INFO|INFO|INFO|INFO|
3|REG2|INFO|INFO|INFO|INFO|INFO|
4|REG2|INFO|INFO|INFO|INFO|INFO|
5|REG1|INFO|INFO|INFO|
6|REG2|INFO|INFO|INFO|INFO|INFO|
7|REG2|INFO|INFO|INFO|INFO|INFO|
8|REG2|INFO|INFO|INFO|INFO|INFO|
9|REG2|INFO|INFO|INFO|INFO|INFO|
10|REG2|INFO|INFO|INFO|INFO|INFO|
11|REG1|INFO|INFO|INFO|
12|REG2|INFO|INFO|INFO|INFO|INFO|
13|REG2|INFO|INFO|INFO|INFO|INFO|
] (delimiter is |);
It will give you this table:
Maybe this will script will help you:
LOAD if(B='REG1',alt(peek('Reg1New'),0)+1,peek('Reg1New')) as Reg1New, *
inline [
A|B|C|D|E|F
1|REG1|INFO|INFO|INFO|
2|REG2|INFO|INFO|INFO|INFO|INFO|
3|REG2|INFO|INFO|INFO|INFO|INFO|
4|REG2|INFO|INFO|INFO|INFO|INFO|
5|REG1|INFO|INFO|INFO|
6|REG2|INFO|INFO|INFO|INFO|INFO|
7|REG2|INFO|INFO|INFO|INFO|INFO|
8|REG2|INFO|INFO|INFO|INFO|INFO|
9|REG2|INFO|INFO|INFO|INFO|INFO|
10|REG2|INFO|INFO|INFO|INFO|INFO|
11|REG1|INFO|INFO|INFO|
12|REG2|INFO|INFO|INFO|INFO|INFO|
13|REG2|INFO|INFO|INFO|INFO|INFO|
] (delimiter is |);
It will give you this table:
Good morning Vegar!
This worked like a charm!
Thank you so much!