topic Re: Grouping Tables into 1 Main Table in QlikView

Grouping Tables into 1 Main Table

SharetteM — Mon, 29 Jun 2020 07:49:57 GMT

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

Re: Grouping Tables into 1 Main Table

zarmoton — Mon, 29 Jun 2020 09:33:41 GMT

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;

Re: Grouping Tables into 1 Main Table

SharetteM — Mon, 29 Jun 2020 11:13:01 GMT

Thank you so much.

This works perfectly