How to make a tour of the rows in a table and assign values ​​to different columns in another table?

[carlos13]

I need to create this structure in the script and do not know how. Always repeated in the data structure

add the qvd

[swuehl]

I think you could start from here:

QVD:

LOAD DATA, %Key_ROW_9533526B5DEFDF5F as Key from Col.qvd (qvd);

RESULT:

LOAD distinct Key resident QVD;

for i = 1 to 19

Left join LOAD Key, DATA as Column$(i)

Resident QVD where mod(recno(),19)=$(i);

next

Hope this helps,

Stefan

[mkirchhofer]

Hi Carlos,

try this source code:

regards

Mario

Directory;
LOAD DATA, 
     %Key_ROW_9533526B5DEFDF5F,
     if(Mod(RowNo(),19) = 1,DATA) as Year,
     if(Mod(RowNo(),19) = 2,DATA) as Comentarios,
     if(Mod(RowNo(),19) = 3,DATA) as Descripcion  
FROM
COL.qvd
(qvd);

[swuehl]

Mario,

I think that will sort the values into columns accordingly, but still create a row per input record, leaving all other columns empty.

Carlos,

if you want to apply the correct field names to my above suggested code, you could use an additional table (I stopped filling in the correct field names after row 3):

QVD:

LOAD DATA, %Key_ROW_9533526B5DEFDF5F as Key from Col.qvd (qvd);

FIELDSMAP:

mapping LOAD * INLINE [

ID, FieldName

1, Year

2, Comentarios

3, Description

4, Field4

5, Field5

6, Field6

7, Field7

8, Field8

9, Field9

10, Field10

11, Field11

12, Field12

13, Field13

14, Field14

15, Field15

16, Field16

17, Field17

18, Field18

19, Field19

];

RESULT:

LOAD distinct Key resident QVD;

for i = 1 to 19

let ColumnName = applymap('FIELDSMAP', $(i));

Left join LOAD Key, DATA as $(ColumnName)

Resident QVD where mod(recno(),19)=$(i);

next

[mkirchhofer · Accepted solution]

Hi Stefan,

you are right but therefor is still the Key in it.

With a group by you can bring them to one line.

Directory;
Temp:
LOAD DATA, 
     %Key_ROW_9533526B5DEFDF5F as Key,
     if(Mod(RowNo(),19) = 1,DATA) as Year,
     if(Mod(RowNo(),19) = 2,DATA) as Comentarios,
     if(Mod(RowNo(),19) = 3,DATA) as Descripcion  
FROM
COL.qvd
(qvd);

COL:
LOAD
 Only(Year),
 Only(Comentarios),
 Only(Descripcion)
Resident Temp
Group By Key;
DROP Table Temp;

many roads to rome. 😉

regards

Mario

[swuehl]

You're right, many many roads...