Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
fnfrbr95
New Contributor II

How to create a Key Field in a Text File - One Too Many

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?

1 Solution

Accepted Solutions
Partner
Partner

Re: How to create a Key Field in a Text File - One Too Many

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:

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

2 Replies
Partner
Partner

Re: How to create a Key Field in a Text File - One Too Many

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:

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

fnfrbr95
New Contributor II

Re: How to create a Key Field in a Text File - One Too Many

Good morning Vegar!

 

This worked like a charm!

 

Thank you so much!