How to transform values in one column to distinct columns (Data from a .csv)

[ni_avoss]

Hi to all,

i am quite new to QV and was reading for a long time here which helped me a lot already.
But now i got to a point where i am stuck and couldn't find any solution yet.

The situation:

I am importing a .csv which got a column which distinct values i want to use as columns, while keeping the other columns.

Maybe this sample is a bit easier to understand:

original excerpt:

user_id	key	value
1	cat1	val1
1	cat2	val2
1	cat3	val3
2	cat1	val4
2	cat2	val5
2	cat3	val6

it want this to be transformed to this:

user_id	cat1	cat2	cat3
1	val1	val2	val3
2	val4	val5	val6

there are other columns (like ids, date) in the original i want to keep as well.

My first thoughts were running throught each line of the original with the help of a switch case/ ifs and checking for the key. but it seems i am running into the problem of concatenating it to the right id/row.

If think it would be great to have something like 'for each $variable in user_id (column)'. but this doesnt work either.

Any ideas? Do I make an obvious mistake?

Best

Alex

[#2]

Hi,

Made some modifications. The script would fail if there was a space or bad charachter in the fieldname-data.

It is now taken care of.

Cheers!

[#3]

Hi ni_avoss,

You can also use the Generic LOAD,

It is a goos solution with out any limitation with numbers of values :

e.g

/*********************************/

INPUT:

LOAD * INLINE

[

user_id, key, value

   1, cat1, val1

   1, cat2, val2

   1, cat3, val3

   2, cat1, val4

   2, cat2, val5

   2, cat3, val6

];

flag:

Generic LOAD

                              user_id,

                              key,

                              value

Resident INPUT;

FOR i = 0 to NoOfTables()

  TableList:

  LOAD TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'flag.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (INPUT) LOAD * RESIDENT $(vTable);

  DROP TABLE $(vTable);

NEXT i

DROP TABLE TableList;

DROP Fields  key,value;

/*********************************/

See the attached qvw.

Hope it help,

Regards,

Yigal.

[salto]

Hello,

I had a similar problem and solved it with that piece of code.

Thank you!