Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SharetteM
Contributor
Contributor

Grouping Tables into 1 Main Table

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_REFCHARGE CODECHARGE VALUE  
35617210013914,29  
356172100096917,01  
35617210018736,03  
356172 0  
356172 0  
356172 0  
356172 0  
356172100260  
356172100250  
356172100220  
356172100200  
35617210010415,24  
356172100110  
356172100120  

 

Hope someone will be able to assist.

Thank you

1 Solution

Accepted Solutions
zarmoton
Creator
Creator

 try it !

Result:

zarmoton_0-1593423206087.png

 

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;

View solution in original post

2 Replies
zarmoton
Creator
Creator

 try it !

Result:

zarmoton_0-1593423206087.png

 

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;

View solution in original post

SharetteM
Contributor
Contributor
Author

Thank you so much.

This works perfectly