Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help: Generate Table from a long string variable with Autogenerate

Hi, I have a long string with lots of values with this format:

LET vData = '28-Mick   -Smith     -X|30-John  -Robert    - -|43-Sonia   -Stone   -X';

So "-" separate fields value and "|" separate Entries and what I want is obtain a tabla like this using Autogenerate.

AgeNameLastNameActve
28MickSmithX
30JohnRobert
43SoniaStoneX

I know the quantity of entries and fields (iRows and iFields). I manage to get it work but hardcoding stuff, I cant manage to make it work in a generic form without hardcoding the number of fields in the subfield statement

For i=1 to $(iRows)

  LET row = SubField('$(vData)','|',i);

  LOAD 

  SubField('$(row)','-',1) AS Age,

  SubField('$(row)','-',2) AS Name,

  SubField('$(row)','-',3) AS LastName,

  SubField('$(row)','-',4) AS Active

  AutoGenerate(1);

Next

Can someone help me pls

Thanks

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I think a preceding load like this would be easiest. A for loop is not necessary.

LET vData = '28-Mick  -Smith    -X|30-John  -Robert    - -|43-Sonia  -Stone  -X';

Data:

LOAD

  trim(SubField(Entry,'-',1)) as Age

  ,trim(SubField(Entry,'-',2)) as Name

  ,trim(SubField(Entry,'-',3)) as LastName

  ,trim(SubField(Entry,'-',4)) as Active

;

LOAD

  SubField('$(vData)','|') as Entry

AutoGenerate 1

;

-Rob

View solution in original post

6 Replies
Nicole-Smith

How about the following?  (Also attached.)

Set vData = '28-Mick   -Smith -X|30-John  -Robert - -|43-Sonia   -Stone   -X';

Let vRows = SubStringCount(vData,'|')+1;

for i=1 to $(vRows)

    Data:

    load SubField(SubField('$(vData)', '|', $(i)), '-', 1) as Age,

     SubField(SubField('$(vData)', '|', $(i)), '-', 2) as Name,
     SubField(SubField('$(vData)', '|', $(i)), '-', 3) as LastName,
     SubField(SubField('$(vData)', '|', $(i)), '-', 4) as Active

    AutoGenerate 1;

next

Not applicable
Author

You can try with IterNo() like below:

Set vData = '28-Mick  -Smith-X|30-John  -Robert- -|43-Sonia  -Stone  -X';

LOAD Trim(SubField(SubField('$(vData)' , '|',IterNo()),'-',1)) AS Age,

      Trim(SubField(SubField('$(vData)' , '|',IterNo()),'-',2)) AS FirstName ,
      Trim(SubField(SubField('$(vData)' , '|',IterNo()),'-',3)) AS LastName ,
      Trim(SubField(SubField('$(vData)' , '|',IterNo()),'-',4))AS Active

AutoGenerate 1

While IterNo() <= SubStringCount('$(vData)','|')+1;

Please find the attached qvw for reference.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I think a preceding load like this would be easiest. A for loop is not necessary.

LET vData = '28-Mick  -Smith    -X|30-John  -Robert    - -|43-Sonia  -Stone  -X';

Data:

LOAD

  trim(SubField(Entry,'-',1)) as Age

  ,trim(SubField(Entry,'-',2)) as Name

  ,trim(SubField(Entry,'-',3)) as LastName

  ,trim(SubField(Entry,'-',4)) as Active

;

LOAD

  SubField('$(vData)','|') as Entry

AutoGenerate 1

;

-Rob

Not applicable
Author

Sorry, perhaps I expressed in the wrong way... English isn't my native language. What I want is to avoid enumerate the "fields number" (marked in red)

For i=1 to $(iRows)

  LET row = SubField('$(vData)','|',i);

  LOAD

  SubField('$(row)','-',1) AS Field1,

  SubField('$(row)','-',2) AS Field2,

  SubField('$(row)','-',3) AS Field3,

  SubField('$(row)','-',4) AS Field4

  AutoGenerate(1);

Next

In this example I have 4 fields but I need to be generic, not always will be 4 fields. Im working with macros to retrieve data from SAP Tables and depending of the Table the field varies. I know the max field number (variable iField) but what I need is to "iterate" them to avoid "hard coding" the fields numbers.

The fields allways will be separated by "-" and the entries with "|" but the number of fields and row are not allways the same.

So I have to make a script Load thats is able to read

LET vData = '28-Mick   -Smith     -X|30-John  -Robert    - -|43-Sonia   -Stone   -X';

Or for example

LET vData = '20140108-Sunny|20140107-Cold|20140107-Snow';

For example the For iterate the iRows wharever the amount... I need that but also for the fields, is there a way to acomplish this ? in another words I want to "emulate" the logic behind having two nested For (one for the rows and another for the fields).

Thx you for you help.

Greetings

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You can use the two parameter version of subfield() to handle a variable number of values.

(Note in the code below, I had to add "Y" at the end of Robert. There is some bug/problem with the entry ending with "-").

LET vData = '28-Mick   -Smith     -X|30-John  -Robert    - Y|43-Sonia   -Stone   -X';

Data:

LOAD

  *

  ,AutoNumber(RecNo(),RecId) as FieldNumber

;

LOAD

  RecNo() as RecId,

  trim(SubField(Entry,'-')) as Field

;

LOAD

  SubField('$(vData)','|') as Entry

AutoGenerate 1

;

I assume you'll have some kind of ordinal field mapping that will let you use this output.

RecId FieldNumber FieldValue
1128
12Mick
13Smith
14X
2130
22John
23Robert
24Y
3143
32Sonia
33Stone
34X

For example:

Final:

LOAD RecId, FieldValue as Age

RESIDENT Data

WHERE FieldNumber=1

;

JOIN (Final)

LOAD RecId, FieldValue as Name

RESIDENT Data

WHERE FieldNumber=2

;

-Rob

Not applicable
Author

Thx Rob, finally we have a limitation with the amount of fields that we retrieve so there has to be explicit so the hardcoding in the LOAD isn't a problem.

So I gave you the correct response for being the best way to implement the question of the thread.

Thx again