topic Re: Converting row to column (another question) in Connectivity & Data Prep

Converting row to column (another question)

Ric75 — Thu, 21 Oct 2021 13:53:09 GMT

Hi to everyone!

I know there're already some questions about this topic but I've tried to write my own script based on the "generic" statement and I couldn't even get to the end of the data loading (it's quite a large table), so I do need some help.

These are an example of my data:

Field	Attribute	Value
X1	A1	V1
X1	A2	V2
X1	A3	V3
X1	A4	V4
X2	A1	V5
X2	A2	V6
X2	A3	V7
X2	A4	V8

Attribute values are not so sequential (A1, A2,...), actually they're codes like 14896,14907, etc...

and I'd like to obtain this:

Field	A1	A2	A3	A4
X1	V1	V2	V3	V4
X2	V5	V6	V7	V8

I do need to transform the data during the load script but I'm stuck at the moment.

I should also "left join" the result table with another table which complicates the use of "generic load" even more.

Any suggestion?

Thanks a lot and kind regards,

Riccardo

Re: Converting row to column (another question)

JGMDataAnalysis — Thu, 21 Oct 2021 17:08:14 GMT

Something like this...

SourceTable: NOCONCATENATE LOAD * INLINE [ Field, Attribute, Value X1, A1, V1 X1, A2, V2 X1, A3, V3 X1, A4, V4 X2, A1, V5 X2, A2, V6 X2, A3, V7 X2, A4, V8 ]; FinalTable: NOCONCATENATE LOAD DISTINCT Field RESIDENT SourceTable; GenericTable: GENERIC LOAD * RESIDENT SourceTable; DROP TABLE SourceTable; FOR i = NoOfTables() - 1 TO 0 STEP -1 LET tname = TableName($(i)); IF SubField(tname, '.', 1) = 'GenericTable' THEN LEFT JOIN (FinalTable) LOAD * RESIDENT $(tname); DROP TABLE $(tname); END IF NEXT i LET i =; LET tname =;

Re: Converting row to column (another question)

Ric75 — Wed, 27 Oct 2021 07:54:50 GMT

Great!

It works great, I just had to tweak a bit the FOR cycle since I had strings in my data:

LET tname = '[' & TableName($(i)) & ']';
IF SubField(tname, '.', 1) = '[GenericTable' THEN

Thank you very much!