3 Replies Latest reply: May 24, 2012 7:25 AM by Evelien Morel RSS

    Left join twice into same field

    Evelien Morel

      Hi all,

       

      I have made 2 left joins. The problem I have is that those 2 joins should put data in the same field "Functional Split"

      In Excel, I used to make this with a vlookup function, combined with an iferror.

       

      IFERROR(VLOOKUP(I22504;'Third Party Freight Costs'!A:B;1;FALSE));VLOOKUP(P22504;'Cost Centers'!A:E;5;FALSE))

       

      I have no idea how to make this work in QV, but what I have tried doesn't work.

       

       

      Here you find the code:

       

      BKNF18:

      LOAD

           'AC'                as "Actuality",

           Bedrijf,

           Journaal,

           Boekjaar,

           Boekstuknummer,

           Boekingslijn,

           Boekingslijn2,

           Period,

           [Account Number],

           Century,

           Year,

           Period2,

           Subledger,

           [Cost Center],

           [Amount Transaction Curr],

           [Amount EUR Curr],

           [D/C],

           key_tgrek,

           If(Bedrijf = 'VIT', 'GRV', 'GRS') as key_GR,

           If(Bedrijf = 'VIT', 'KPV', 'KPS') as key_KP,

           [Account Number] & '-' &  If(Bedrijf = 'VIT', 'GRV', 'GRS')                as key_GR_Account,

           [Cost Center] & '-' & If(Bedrijf = 'VIT', 'KPV', 'KPS')                    as key_KP_CostCenter,

           Bedrijf & '-' & [Cost Center]                                                as "Comp-Cost"

       

      FROM

      [C:\QlikView\QV Storage\DATA\QVD\BKNF18.QVD]

      (qvd);

       

      // Functional Split Cost Center ==========================================================================

      Directory;

      Left Join (BKNF18)

      LOAD

           Company & '-' & RKNR06            as "Comp-Cost",

           Company,

           RKNR06,

           [Functional Split]                as "Functional Split"

       

      FROM

      [..\QvDATA\AAA SQL (DETAIL COST) VIT + SNS + NUSC 2012 (NEW-original) Prep BU Follow up (TO DO) EMO.xlsx]

      (ooxml, embedded labels, header is 1 lines, table is [Cost Centers]);

       

      // Functional Split Account Number =========================================================================

       

      Directory;

      Left join (BKNF18)

      LOAD

           [Account Number],

           [Functional Split]            as "Functional Split"

       

      FROM

      [..\QvDATA\AAA SQL (DETAIL COST) VIT + SNS + NUSC 2012 (NEW-original) Prep BU Follow up (TO DO) EMO.xlsx]

      (ooxml, embedded labels, table is [Third Party Freight Costs]);

       

       

       

      Any help would be welcome!

      Thanx in advace,

       

      Evelien

        • Left join twice into same field
          Evelien Morel

          Hi,

           

          since there is no answer to my question, I must be asking a difficult one.

           

          If anyone has any idea, I still have found no solution.

           

          Thanks in advance!

            • Re: Left join twice into same field
              Teemu Pitkänen

              Hello,

               

              It's certainly doable but I think we need a little more information before we can help Do you want the join to happen in a certain order (for example if nothing is joined from the first table, join from the second)? Is joining multiple values allowed in case there is more than one match etc?

               

              Assuming you want only one value and the first table is higher in priority, you could use applymap to "join" the data. Something like this might work:

               

              CostCenterMap:

              MAPPING

              LOAD

                   Company & '-' & RKNR06            as "Comp-Cost",

                   [Functional Split]                as "Functional Split"

               

              FROM

              [..\QvDATA\AAA SQL (DETAIL COST) VIT + SNS + NUSC 2012 (NEW-original) Prep BU Follow up (TO DO) EMO.xlsx]

              (ooxml, embedded labels, header is 1 lines, table is [Cost Centers]);

               

              AccountMap:

              MAPPING

              LOAD

                   [Account Number],

                   [Functional Split]            as "Functional Split"

               

              FROM

              [..\QvDATA\AAA SQL (DETAIL COST) VIT + SNS + NUSC 2012 (NEW-original) Prep BU Follow up (TO DO) EMO.xlsx]

              (ooxml, embedded labels, table is [Third Party Freight Costs]);

               

              And when loading the fact table use something like this:

               

              BKNF18:

              LOAD

              ...

              Applymap('CostCenterMap', Bedrijf & '-' & [Cost Center], Applymap('AccountMap', [Account Number], 0)) AS [Functional Split],

              ...

               

              This will try to map a value from the first mapping table and if no match is found, it uses the second table. 0 is set if no match is found from either table.

               

              I hope this helps!

               

              -Teemu