Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Reverse-Crosstable Transformation? | Qlik Community
I am attaching data in attachment!
Regards
Sushant
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.
Well i want to do in script, as i have further more transformation to done on data!
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,
crosstable transformation can do this
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:
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
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;