Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
can you post sample data in excel with each tab representing your table and also a table that shows your expected final output
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);
Thanks would this be possible using resident tables instead of storing them as QVD, my data is quite large.
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
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
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