topic Re: Transforming rows values to column! in QlikView

Transforming rows values to column!

imsushantjain — Fri, 17 Aug 2018 06:13:01 GMT

Hello!

I have a data snapshot like this :

For each "exp_line_id" i have unique value of "current exposure", and i have many "exp_line_id" and "short_name"

only showing you the snapshot of data.

I want to convert my data to this format:

As you see i have removed "exp_line_id" from the final output as its no longer relevant once i have transformed my data to this format once i have loop through entire "exp_line_id" in my records.

How can i achieve this?

I tried these two blog's, but unable to achieve the desired result, but still not able to write the code that works!

The Generic Load

Reverse-Crosstable Transformation? | Qlik Community

I am attaching data in attachment!

Regards

Sushant

Re: Transforming rows values to column!

jonathandienst — Fri, 17 Aug 2018 06:20:40 GMT

You should be able to display this format easily enough in the front end without having to reshape it in the load script. Just use a pivot table with risk_expdef_name, criteria_name, short_name as dimensions and drag the first to the top of the table.

Re: Transforming rows values to column!

imsushantjain — Fri, 17 Aug 2018 06:27:03 GMT

Well i want to do in script, as i have further more transformation to done on data!

Re: Transforming rows values to column!

PrashantSangle — Fri, 17 Aug 2018 06:49:38 GMT

try below

Load criteria_name,

short_name,

current_expos as Cumulative_loss_limit

where risk_def_name='Cumulative Loss Limit'

left join

Load criteria_name,

short_name,

current_expos as cut_loss_limit

where risk_def_name='Cut Loss Limit'

left join

Load criteria_name,

short_name,

current_expos as open_position_limit_max

where risk_def_name='Open Position Limit Max'

...

Regards,

Re: Transforming rows values to column!

manoj217 — Fri, 17 Aug 2018 12:34:38 GMT

crosstable transformation can do this

Re: Transforming rows values to column!

BalaBhaskar_Qlik — Fri, 17 Aug 2018 13:30:37 GMT

Try Generic load like this:

ABC:

GENERIC LOAD * INLINE

[

items, stores, quantity

A, 1, 5

A, 10, 1

B, 1, 2

C, 10, 3

];

It'll convert the data.

Check this:

How convert table from rows to columns | Qlik Community

Re: Transforming rows values to column!

MarcoWedel — Fri, 17 Aug 2018 21:25:52 GMT

another example using a generic load of your sample data:

table1:
Generic
LOAD criteria_name,
    short_name,
    risk_expdef_name,
    current_exposure
FROM [https://community.qlik.com/servlet/JiveServlet/download/1533319-335666/Row%20values%20to%20column%20conversion.xlsx] (ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(Remove(Row, Pos(Top, 15)),Remove(Row, Pos(Top, 14)),Remove(Row, Pos(Top, 13)),Remove(Row, Pos(Top, 12))));

hope this helps

regards

Marco

Re: Transforming rows values to column!

nsetty — Tue, 21 Aug 2018 03:59:09 GMT

InputTable:

LOAD

risk_expdef_name,

exp_line_id,

criteria_name,

short_name,

current_exposure

FROM [lib://mypath/Row values to column conversion.xlsx]

(ooxml, embedded labels, table is Sheet1);

GenericLabel:

Generic

LOAD

criteria_name,

short_name,

risk_expdef_name,

current_exposure

Resident InputTable;

ResultTable:

LOAD Distinct criteria_name Resident InputTable;

FOR i = 0 to NoOfTables()

TableList:

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

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

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

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

LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

NEXT i

DROP TABLES TableList, InputTable;