Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
Next
Can someone help me pls
Thanks
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
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
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.
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
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
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
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