4 Replies Latest reply: May 7, 2012 7:39 AM by Evelien Morel RSS

    Script Where if

    Evelien Morel

      Hi all,

       

      i''m trying to left join two tables, but I'm facing problems.

      I'm working with data from 3 different companies (VIT, SNS, NUSC).

      The data I want to join is different for the 3 companies but comes out of the same table.

       

      Here you see my code:

       

      BKNF18:

      LOAD

           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

      FROM

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

      (qvd);

       

      // REKF Account) number =========================================================================

      left join (BKNF18)

      LOAD

          RKNR06 as [Account Number],

          RKNR06,

          RSKD06,

          RKNM06                    as "Account Name"

      where if(Bedrijf = "VIT", RSKD06 = "GRV", RSKD06 = "GRS")

      ;

      SQL SELECT *

      FROM VITAMEXA.AAADTA.REKF06;

       

       

      'Bedrijf' is the key field that defines the different companies (VIT, SNS, NUSC)

      'RKSD06' is the field that determines to which company the data belongs. (GRV for company 'VIT', GRS for company 'SNS', ...)

       

      When I run this code, I get the message 'Field not found' <Bedrijf>

       

      Any ideas how I get this code running?

       

      Thanks!

        • Re: Script Where if
          Jonathan Dienst

          Hi

           

          Bedrijf does not exist in the data returned from your SQL SELECT statement, so you cannot use it here.

           

          I am not sure of your data structure, but based on what you have posted I suggest the following.

           

          • Remove the where-if line from the second LOAD. I suspect that this statement would not be doing what you are expecting anyway, but you cant use Bedrijf here.
          • Add this line to the first LOAD (where Bedrijf does exist:

                   

                         If(Bedrijf = 'VIT', 'GRV', 'GRS') As RSKD06

           

          Hope that helps

          Jonathan

            • Re: Script Where if
              Evelien Morel

              Hi Jonathan,

               

              I tried your solution but it doesn't do what I expect it to do.

               

              When the company is 'VIT', then the field RSKD06 is GRV.

              For the other companies, the field RSKD06 is blank. I would expect to see GRS there.

               

              If I can get this working, I can make a key field to make the join with the REKF06 table.

               

              What have I done wrong?

               

              Here the adjusted code:

               

              BKNF18:

              LOAD

                   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 RSKD06

                  

              FROM

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

              (qvd);