Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Please find attached a sample of my data set.
I import my data from an Excel document but the structure of the data set is not optimal for me, that is why i would like to transpose the data into three columns such as PC, Notions and Val.
-> Notions will be the list of all column on my data set which start with "Pnl_Notion/...."
How can i do that into QlikView without modify the Excel file please ?
Thanks you in advance for your help.
Sébastien.
Or this if you want to get rid of all those rows where Value = 0
Table:
CrossTable(Type, Value)
LOAD Pc,
[Pnl_Notion/CFP-CMM/EUR],
[Pnl_Notion/CFP-CMM/USD],
[Pnl_Notion/E.D.M./EUR],
[Pnl_Notion/E.D.M./USD],
[Pnl_Notion/Final Result Acc/EUR],
[Pnl_Notion/Final Result Acc/USD],
[Pnl_Notion/Net Trade Result/EUR],
[Pnl_Notion/Net Trade Result/USD],
[Pnl_Notion/Reallo CVA / DVA/EUR],
[Pnl_Notion/Reallo CVA / DVA/USD],
[Pnl_Notion/Retro Cooperation 2/EUR],
[Pnl_Notion/Retro Cooperation 2/USD],
[Pnl_Notion/SLS Final Result/EUR],
[Pnl_Notion/SLS Final Result/USD],
[Pnl_Notion/SMTD Impact/EUR],
[Pnl_Notion/SMTD Impact/USD],
[Pnl_Notion/SR IN TRD/EUR],
[Pnl_Notion/SR IN TRD/USD],
[Pnl_Notion/Sales Credits Newedge/EUR],
[Pnl_Notion/Sales Credits Newedge/USD]
FROM
[Data Test.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Data AIR]);
FinalTable:
NoConcatenate
LOAD *
Resident Table
Where Value <> 0;
DROP Table Table;
Use CrossTable Load: The Crosstable Load
Check this:
Table:
CrossTable(Type, Value)
LOAD Pc,
[Pnl_Notion/CFP-CMM/EUR],
[Pnl_Notion/CFP-CMM/USD],
[Pnl_Notion/E.D.M./EUR],
[Pnl_Notion/E.D.M./USD],
[Pnl_Notion/Final Result Acc/EUR],
[Pnl_Notion/Final Result Acc/USD],
[Pnl_Notion/Net Trade Result/EUR],
[Pnl_Notion/Net Trade Result/USD],
[Pnl_Notion/Reallo CVA / DVA/EUR],
[Pnl_Notion/Reallo CVA / DVA/USD],
[Pnl_Notion/Retro Cooperation 2/EUR],
[Pnl_Notion/Retro Cooperation 2/USD],
[Pnl_Notion/SLS Final Result/EUR],
[Pnl_Notion/SLS Final Result/USD],
[Pnl_Notion/SMTD Impact/EUR],
[Pnl_Notion/SMTD Impact/USD],
[Pnl_Notion/SR IN TRD/EUR],
[Pnl_Notion/SR IN TRD/USD],
[Pnl_Notion/Sales Credits Newedge/EUR],
[Pnl_Notion/Sales Credits Newedge/USD]
FROM
[Data Test.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Data AIR]);
Or this if you want to get rid of all those rows where Value = 0
Table:
CrossTable(Type, Value)
LOAD Pc,
[Pnl_Notion/CFP-CMM/EUR],
[Pnl_Notion/CFP-CMM/USD],
[Pnl_Notion/E.D.M./EUR],
[Pnl_Notion/E.D.M./USD],
[Pnl_Notion/Final Result Acc/EUR],
[Pnl_Notion/Final Result Acc/USD],
[Pnl_Notion/Net Trade Result/EUR],
[Pnl_Notion/Net Trade Result/USD],
[Pnl_Notion/Reallo CVA / DVA/EUR],
[Pnl_Notion/Reallo CVA / DVA/USD],
[Pnl_Notion/Retro Cooperation 2/EUR],
[Pnl_Notion/Retro Cooperation 2/USD],
[Pnl_Notion/SLS Final Result/EUR],
[Pnl_Notion/SLS Final Result/USD],
[Pnl_Notion/SMTD Impact/EUR],
[Pnl_Notion/SMTD Impact/USD],
[Pnl_Notion/SR IN TRD/EUR],
[Pnl_Notion/SR IN TRD/USD],
[Pnl_Notion/Sales Credits Newedge/EUR],
[Pnl_Notion/Sales Credits Newedge/USD]
FROM
[Data Test.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Data AIR]);
FinalTable:
NoConcatenate
LOAD *
Resident Table
Where Value <> 0;
DROP Table Table;
Thanks a lot ! it is exactly that .
Just one more question, how can i keep just the name of the Indicator, so the string between the "/" for example:
[Pnl_Notion/Reallo CVA / DVA/EUR] ==> Reallo CVA/DVA
Then i would like to create a table, named Currency, which will select the indicators if it is "EUR" or "USD"
Thanks you in advance for your help,
Sébastien.
Try this:
Table:
CrossTable(Type, Value)
LOAD Pc,
[Pnl_Notion/CFP-CMM/EUR],
[Pnl_Notion/CFP-CMM/USD],
[Pnl_Notion/E.D.M./EUR],
[Pnl_Notion/E.D.M./USD],
[Pnl_Notion/Final Result Acc/EUR],
[Pnl_Notion/Final Result Acc/USD],
[Pnl_Notion/Net Trade Result/EUR],
[Pnl_Notion/Net Trade Result/USD],
[Pnl_Notion/Reallo CVA / DVA/EUR],
[Pnl_Notion/Reallo CVA / DVA/USD],
[Pnl_Notion/Retro Cooperation 2/EUR],
[Pnl_Notion/Retro Cooperation 2/USD],
[Pnl_Notion/SLS Final Result/EUR],
[Pnl_Notion/SLS Final Result/USD],
[Pnl_Notion/SMTD Impact/EUR],
[Pnl_Notion/SMTD Impact/USD],
[Pnl_Notion/SR IN TRD/EUR],
[Pnl_Notion/SR IN TRD/USD],
[Pnl_Notion/Sales Credits Newedge/EUR],
[Pnl_Notion/Sales Credits Newedge/USD]
FROM
[Data Test.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Data AIR]);
FinalTable:
NoConcatenate
LOAD Pc,
Left(Mid(Type, Index(Type, '/') + 1), Len(Mid(Type, Index(Type, '/') + 1)) - 4) as Type,
Value
Resident Table
Where Value <> 0;
My bad, did not read the Currency requirement
Table:
CrossTable(Type, Value)
LOAD Pc,
[Pnl_Notion/CFP-CMM/EUR],
[Pnl_Notion/CFP-CMM/USD],
[Pnl_Notion/E.D.M./EUR],
[Pnl_Notion/E.D.M./USD],
[Pnl_Notion/Final Result Acc/EUR],
[Pnl_Notion/Final Result Acc/USD],
[Pnl_Notion/Net Trade Result/EUR],
[Pnl_Notion/Net Trade Result/USD],
[Pnl_Notion/Reallo CVA / DVA/EUR],
[Pnl_Notion/Reallo CVA / DVA/USD],
[Pnl_Notion/Retro Cooperation 2/EUR],
[Pnl_Notion/Retro Cooperation 2/USD],
[Pnl_Notion/SLS Final Result/EUR],
[Pnl_Notion/SLS Final Result/USD],
[Pnl_Notion/SMTD Impact/EUR],
[Pnl_Notion/SMTD Impact/USD],
[Pnl_Notion/SR IN TRD/EUR],
[Pnl_Notion/SR IN TRD/USD],
[Pnl_Notion/Sales Credits Newedge/EUR],
[Pnl_Notion/Sales Credits Newedge/USD]
FROM
[Data Test.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Data AIR]);
FinalTable:
NoConcatenate
LOAD Pc,
Left(Mid(Type, Index(Type, '/') + 1), Len(Mid(Type, Index(Type, '/') + 1)) - 4) as Type,
SubField(Type, '/', -1) as Currency,
Value
Resident Table
Where Value <> 0;
DROP Table Table;
Thanks a lot but here i have two identical Notions for example Final Result Acc.
How can i select the currency EUR or USD ?
Thanks in advance,
Sébastien.
Like this?