6 Replies Latest reply: Jan 4, 2017 4:15 PM by Massimo Grossi RSS

    Compound Fields with a Join

    Bill Uran

      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!!

        • Re: Compound Fields with a Join
          Vineeth Pujari

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

          • Re: Compound Fields with a Join
            Massimo Grossi

            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);

              • Re: Compound Fields with a Join
                Bill Uran

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

                  • Re: Compound Fields with a Join
                    Massimo Grossi

                    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


                      • Re: Compound Fields with a Join
                        Bill Uran

                        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

                          • Re: Compound Fields with a Join
                            Massimo Grossi

                            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