Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
billuran
Partner - Creator
Partner - Creator

Compound Fields with a Join

Hello all, I have 2 fact tables and one mapping table.

Fact table 1 and 2 have 2 fields in common; Field 1 and Field 2

Fact table 2 has field 3, where i want to join Field 4 to from a mapping table

Fact Table 1 has the same Field4 in it.

I want to create a concatenation of Field1&Field2&Field4, but field 4 doesn't exist directly in table 2 without a join and yet I still cant make it work.

How can I create the concatenation when the field doesn't exist in the load of field 1 and 2 in table 2.

Thanks!!

6 Replies
vinieme12
Champion III
Champion III

can you post sample data in excel with each tab representing your table and also a table that shows  your expected final output

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
maxgro
MVP
MVP

DIRECTORY;

//

// make some test data

//

Table1:

load * inline [

field1, field2, field4

1,1,1

2,2,2

3,3,3

];

Table2:

load * inline [

field1, field2, field3

11,11,a

12,12,b

13,13,c

];

TableMap:

load * inline [

from, to

a,11

b,12

c,13

];

STORE Table1 into Table1.qvd (qvd);

STORE Table2 into Table2.qvd (qvd);

STORE TableMap into TableMap.qvd (qvd);

DROP Tables Table1, Table2, TableMap;

//

// example using test data

//

Map:

Mapping LOAD from, to

FROM TableMap.qvd (qvd);

Table:

LOAD field1, field2, field4

FROM Table1.qvd (qvd);

Concatenate (Table)

LOAD field1, field2, ApplyMap('Map', field3) as field4

FROM Table2.qvd (qvd);

billuran
Partner - Creator
Partner - Creator
Author

Thanks would this be possible using resident tables instead of storing them as QVD, my data is quite large.

maxgro
MVP
MVP

I used qvd for my example because I don't have your data.

You can replace the load from qvd with resident load.

Maybe

Map:

Mapping LOAD from, to

resident ???;

Table:

LOAD field1, field2, field4

resident ???;

Concatenate (Table)

LOAD field1, field2, ApplyMap('Map', field3) as field4

resident ???;


or post your script or a small extract if you want some more help


billuran
Partner - Creator
Partner - Creator
Author

Here is my script:

PP_Map:

LOAD `Service_Date`,

    PP;

SQL SELECT `Service_Date`,

    PP

FROM HCOPT.PPE;

Volume:

LOAD `Charged_BU`,

    `Charged_deptid`,

    `Charge Code`,

    `Service Units`,

    `Service_Date`;

SQL SELECT Charged_BU`,

    `Charge Code`,

    `Service Units`,

    `Service_Date`,

    `Charged_deptid`

FROM HCOPT.CDM;

LIB CONNECT TO 'Google Cloud (hcopt_senseadmin)';

Payroll:

LOAD Emplid,

    `Charged_BU`,

    `Charged_deptid`,

    `Hours`,

     PP;

SQL SELECT Emplid,

    Emplid,

    `Charged_BU`,

    `Charged_deptid`,

    `Hours`,

     PP

FROM HCOPT.Payroll;

The goal is to not have nay synthetic keys nor circular references, so i need to first join PP_Map to service date to get the appropriate PP for each service date, then get rid of the generated synthetic keys for Charge_BU and Charge_deptid

maxgro
MVP
MVP

so i need to first join PP_Map to service date to get the appropriate PP for each service date,

PP_Map:

Mapping LOAD `Service_Date`, PP;

SQL SELECT `Service_Date`,    PP    FROM HCOPT.PPE;

Volume:

LOAD `Charged_BU`,

    `Charged_deptid`,

    `Charge Code`,

    `Service Units`,

    ApplyMap('PP_Map'', `Service_Date`) as PP

    ;

SQL SELECT Charged_BU`,

    `Charge Code`,

    `Service Units`,

    `Service_Date`,

    `Charged_deptid`

FROM HCOPT.CDM;

LIB CONNECT TO 'Google Cloud (hcopt_senseadmin)';

Payroll:

LOAD

    Emplid,

    `Charged_BU`,

    `Charged_deptid`,

    `Hours`,

    PP;

SQL SELECT Emplid,

    Emplid,

    `Charged_BU`,

    `Charged_deptid`,

    `Hours`,

    PP

FROM HCOPT.Payroll;

then get rid of the generated synthetic keys for Charge_BU and Charge_deptid

you can stay with the synthetic key (read Synthetic Keys‌‌)

if you want to remove, you can replace the 2 fields

    `Charged_BU`,

    `Charged_deptid`,

with a concatenated field

    'Charged_BU' & '|' & 'Charged_deptid' as NewField

But it seems there are another common field, PP, what's this? If it's a dimension maybe you also need to concatenate this

    'Charged_BU' & '|' & 'Charged_deptid' & '|' & PP as NewField

Here you can find a lot of useful info on data modeling in Qlik

Get started with developing qlik datamodels