topic how to store multiple row data in single coulmn in QlikView

how to store multiple row data in single coulmn

sushil353 — Wed, 11 Jan 2012 11:39:34 GMT

Hi all,

I have a source table in which when an error occured, related error msg is stored in multiple rows. how can i store all msgs in single row/coulmn

Please find the attached sample application.

Re: how to store multiple row data in single coulmn

derekjones — Wed, 11 Jan 2012 11:57:29 GMT

Hi Sushil

Use a concat in the load.

LOAD

BSN as bsn,
'Runtime_error_occured' as error,

concat(Measure,' ') as measure

RESIDENT source_table group by BSN;

See attached amended script in your qvw,

Hope this helps.

Derek

how to store multiple row data in single coulmn

sushil353 — Wed, 11 Jan 2012 17:32:16 GMT

Hi Derek,

Thanks for you valuable reply,

It seems it is done correctly but in measure field error msgs are not concatenated in order.

they should be concatenated in order as given in the source table...

Please help me to sort out this issue.

Thanks

Sushil

Re: how to store multiple row data in single coulmn

derekjones — Wed, 11 Jan 2012 17:45:12 GMT

OK, possibly slightly more complicated then.

You need to add a record count to your original table so it has a numeric value of the load order, from this you can the use this 'RecordNo' to fix the order of the concatenation.

Attached is your example with the required fix, however if you are bringing in the raw data from a text file/qvd etc, it's much easier than the script I've used as you won't need to create a temp table, you can simply just add RecNo() as RecordNo to the start of your load.

Hope this helps

Derek