Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (3)
1 Solution

Accepted Solutions

Re: Help: Generate Table from a long string variable with Autogenerate

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

6 Replies

Re: Help: Generate Table from a long string variable with Autogenerate

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

Re: Help: Generate Table from a long string variable with Autogenerate

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.

Re: Help: Generate Table from a long string variable with Autogenerate

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

Re: Help: Generate Table from a long string variable with Autogenerate

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

Re: Help: Generate Table from a long string variable with Autogenerate

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

Re: Help: Generate Table from a long string variable with Autogenerate

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

Community Browser