5 Replies Latest reply: May 9, 2018 2:53 AM by Anthony Barbier RSS

    Resident where on an other table

    Anthony Barbier

      Hello,

      i have one table

      For some reason i have to  load this table with a where clause like this:

       

      TABLE:

      LOAD

          ID,

          DATEX,   

          CREF&BP as %key

      FROM lib

      WHERE not(DSTA=1 and OSTA=2);

       

      Now i want to add at this table the items where DSTA=1 and OSTA=2 and the date must be > at the date where not(DSTA=1 and OSTA=2) and the compare must be done on CREF&BP.

      So i tried something like that


      NoConcatenate

      TABLE1:

      LOAD

          ID,

          DATEX as DATE1,   

          CREF&BP as %key1

      FROM lib

      WHERE DSTA=1 and OSTA=2;

       

      Concatenate(TABLE)

           ID,

          DATE1 as DATEX,   

          %key1 as %key

      resident TABLE1

      WHERE not Exists(%KEY,%KEY1) and DATE1 > DATEX;

       

      But Qlik sense say DATEX not exist

       

      Help please

        • Re: Resident where on an other table
          Mark Ritter

          I can't think of any reason why you would need the where clause.  Do you get an error message or something?

          • Re: Resident where on an other table
            Shivanand Kamath

            DATE1 > DATEX will not work as DATEX does not exist in the table TABLE1

            • Re: Resident where on an other table
              Cheenu Janakiram

              Shivanand is right. Only Date1 exists in your 2nd table whilst loading (Datex exists once the table is loaded and the field aliasing is complete). Please explain what you are trying to do, as your script seems a bit convoluted.

               

              Tcho,

               

              C  ;o)

              • Re: Resident where on an other table
                Massimo Grossi

                I think you can add the TABLE date to TABLE1 (I assume CREF&BP is the key), bold in the script below


                NoConcatenate

                TABLE1:

                LOAD

                    ID,

                    DATEX as DATE1, 

                    CREF&BP as %key1

                FROM lib

                WHERE DSTA=1 and OSTA=2;

                 

                left join (TABLE1) LOAD

                    CREF&BP as %key1,

                    DATEX as DATE2

                  resident TABLE;

                 

                and then concatenate with the DATE1 > DATE2 condition

                ......

                • Re: Resident where on an other table
                  Anthony Barbier

                  Oh sorry i don t explain my intention.

                  I want to add a flag to my table so when i create TABLE i add 0 as flag.

                  Now i want to add 1 as flag to all data with DSTA=1 and OSTA=2 where for the key he haven t clone with date > or =. It have to delete all over data

                  Example i have this datas

                      ID    DATEX    CREF    BP      DSTA      OSTA

                      1      10-30-17  SMITH  330        1               2

                       2     11-15-17  SMITH  200         3              2

                       3     11-15-17   JOBS   150         1              2

                       4     11-15-17   JOBS   150         2              2

                       5     11-15-17    BYE     660        1              2 

                       6     10-01-17    BYE     660        2              2

                       7     10-30-17   MONK  550         1              2

                       8     11-15-17   MONK  550         3              2


                  It should give me :

                      

                      ID    DATEX    CREF    BP      DSTA      OSTA     FLAG

                      1      10-30-17  SMITH  330        1               2          1

                       2     11-15-17  SMITH  200         3              2          0

                       3     11-15-17   JOBS   150         1              2          1

                       4     11-15-17   JOBS   150         2              2          0

                       5     11-15-17    BYE     660        1              2          1

                       6     10-01-17    BYE     660        2              2          0

                       8     11-15-17   MONK  550         3              2          0


                  Thanks

                  I will try something with your solution Massimo