topic Help: Generate Table from a long string variable with Autogenerate in QlikView

Help: Generate Table from a long string variable with Autogenerate

Tue, 07 Jan 2014 19:47:58 GMT

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.

Age	Name	LastName	Actve
28	Mick	Smith	X
30	John	Robert
43	Sonia	Stone	X

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);

Can someone help me pls

Thanks

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

Nicole-Smith — Tue, 07 Jan 2014 19:59:13 GMT

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

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

Wed, 08 Jan 2014 02:52:32 GMT

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

rwunderlich — Wed, 08 Jan 2014 04:20:12 GMT

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

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

Wed, 08 Jan 2014 04:57:09 GMT

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);

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

rwunderlich — Wed, 08 Jan 2014 07:44:56 GMT

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
1	1	28
1	2	Mick
1	3	Smith
1	4	X
2	1	30
2	2	John
2	3	Robert
2	4	Y
3	1	43
3	2	Sonia
3	3	Stone
3	4	X

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

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

Wed, 08 Jan 2014 23:37:01 GMT

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