Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
Hope you are able to assist. I have been searching the blogs and could not find an answer to my problem.
I currently have 30 columns that I would like to combine in to 2 columns.
The first 15 columns are the charge codes and the last 15 columns are the actual charge values.
I have attached sample model to show you how I pull the data from the database.
Can you please help me with any suggestions on how you would do this as currently If statement or the Pick(match()) statement will not work as there is to much data.
The End result I am looking for is as follows
COMPUTER_REF | CHARGE CODE | CHARGE VALUE | ||
356172 | 10013 | 914,29 | ||
356172 | 10009 | 6917,01 | ||
356172 | 10018 | 736,03 | ||
356172 | 0 | |||
356172 | 0 | |||
356172 | 0 | |||
356172 | 0 | |||
356172 | 10026 | 0 | ||
356172 | 10025 | 0 | ||
356172 | 10022 | 0 | ||
356172 | 10020 | 0 | ||
356172 | 10010 | 415,24 | ||
356172 | 10011 | 0 | ||
356172 | 10012 | 0 |
Hope someone will be able to assist.
Thank you
try it !
Result:
T1:
CrossTable(CHARGE1, CHARGE_VALUE)
LOAD COMPUTER_REF,
CHARGE1,
CHARGE10,
CHARGE11,
CHARGE12,
CHARGE13,
CHARGE14,
CHARGE15,
CHARGE2,
CHARGE3,
CHARGE4,
CHARGE5,
CHARGE6,
CHARGE7,
CHARGE8,
CHARGE9
FROM
[F:\Qlikview\QV SYSTEM MONITOR\Mapping - Reverse Eng\Test Model Sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);
T2:
CrossTable(CHARGE_CODE1, Data_code)
LOAD COMPUTER_REF,
CHARGE_CODE1,
CHARGE_CODE10,
CHARGE_CODE11,
CHARGE_CODE12,
CHARGE_CODE13,
CHARGE_CODE14,
CHARGE_CODE15,
CHARGE_CODE2,
CHARGE_CODE3,
CHARGE_CODE4,
CHARGE_CODE5,
CHARGE_CODE6,
CHARGE_CODE7,
CHARGE_CODE8,
CHARGE_CODE9
FROM
[F:\Qlikview\QV SYSTEM MONITOR\Mapping - Reverse Eng\Test Model Sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);
T2Bis:
NoConcatenate
load COMPUTER_REF,
replace(CHARGE_CODE1,'_CODE', '') as CHARGE_CODE1,
Data_code
Resident T2;
drop table T2;
T3:
left join (T1)
load
COMPUTER_REF,
CHARGE_CODE1 as CHARGE1,
Data_code as CHARGE_CODE
resident T2Bis;
drop Field CHARGE1;
drop Table T2Bis;
try it !
Result:
T1:
CrossTable(CHARGE1, CHARGE_VALUE)
LOAD COMPUTER_REF,
CHARGE1,
CHARGE10,
CHARGE11,
CHARGE12,
CHARGE13,
CHARGE14,
CHARGE15,
CHARGE2,
CHARGE3,
CHARGE4,
CHARGE5,
CHARGE6,
CHARGE7,
CHARGE8,
CHARGE9
FROM
[F:\Qlikview\QV SYSTEM MONITOR\Mapping - Reverse Eng\Test Model Sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);
T2:
CrossTable(CHARGE_CODE1, Data_code)
LOAD COMPUTER_REF,
CHARGE_CODE1,
CHARGE_CODE10,
CHARGE_CODE11,
CHARGE_CODE12,
CHARGE_CODE13,
CHARGE_CODE14,
CHARGE_CODE15,
CHARGE_CODE2,
CHARGE_CODE3,
CHARGE_CODE4,
CHARGE_CODE5,
CHARGE_CODE6,
CHARGE_CODE7,
CHARGE_CODE8,
CHARGE_CODE9
FROM
[F:\Qlikview\QV SYSTEM MONITOR\Mapping - Reverse Eng\Test Model Sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);
T2Bis:
NoConcatenate
load COMPUTER_REF,
replace(CHARGE_CODE1,'_CODE', '') as CHARGE_CODE1,
Data_code
Resident T2;
drop table T2;
T3:
left join (T1)
load
COMPUTER_REF,
CHARGE_CODE1 as CHARGE1,
Data_code as CHARGE_CODE
resident T2Bis;
drop Field CHARGE1;
drop Table T2Bis;
Thank you so much.
This works perfectly