Transforming rows values to column!

[imsushantjain]

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

[jonathandienst]

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.

[imsushantjain]

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

[PrashantSangle]

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,

[manoj217]

crosstable transformation can do this

[balabhaskarqlik]

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

[MarcoWedel]

Hi

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%20...] (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

[nsetty]

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;