6 Replies Latest reply: Feb 7, 2012 2:29 AM by Jeanine Mouton RSS

    Nested SQL select

    Jeanine Mouton

      I am new to Qlikview and I need help to create a single table from 2 related tables with no common field. This is what I have:

       

      Table A fields: SETNAME, VALFROM, VALTO

      Table B field: KSTAR

      e.g.

       

      SETNAME    VALFROM    VALTO

      Wages          1000            1004

      Tea               1005            1009

       

      KSTAR

      1000

      1001

      1002

      1003

      1004

      1005

      .....

       

      I need to find the SETNAME (table A) for each KSTAR (Table B) where KSTAR falls within the VALFROM, VALTO range.

      Resultant table should look like this:

       

      SETNAME

      Wages 1000

      Wages 1001

      Wages 1002

      ...

      Tea       1005

      ....

       

      Thank you!

      Jeanine

        • Nested SQL select
          Vijay Kumar

          Hi,

          Please go thru this code. Hope this will help u.

           

          T1:
          LOAD * INLINE [
              SETNAME, VALFROM,VALTO
              Wages, 1000,1004
              Tea,1005,1009
          ];

          T2:

          LOAD * INLINE [
          KSTAR
          1000
          1001
          1002
          1003
          1004
          1005];

          T3:
          NoConcatenate
          Load
          KSTAR
          Resident T2;
          Left Join
          IntervalMatch(KSTAR)
          LOad
          VALFROM,
          VALTO

          Resident T1;
          Left Join
          LOad
          VALFROM,
          VALTO,
          SETNAME
          Resident T1;
          Drop Table T1,T2;

            • Nested SQL select
              Jeanine Mouton

              Thank you for your reply.

              I don't think I made myself clear on the values of the tables. The example given is just a few values. Each of the tables have many values and can change at any time. I cannot load fixed values for these tables.

              I have to load the values from the dbo tables.

              Can I still use your code?

              • Re: Nested SQL select
                Jeanine Mouton

                Thank you for your reply.

                I don't think I made myself clear on the values of the tables. The example given is just a few values. Each of the tables have many values and can change at any time. I cannot load fixed values for these tables.

                I have to load the values from the dbo tables.

                Can I still use your code?

                  • Nested SQL select
                    Vijay Kumar

                    Sure...You can use same from Dbo table.

                     

                    T1:

                    Load

                    *;

                    SQL Select

                    * from ABC;

                     

                    T2:

                    Load

                    *;

                    SQL Select

                    * From DEF;

                     

                    After this everything is same

                      • Re: Nested SQL select
                        Jeanine Mouton

                        Hi Vijay,

                         

                        thank you for your answer - it works perfectly!!

                         

                        I have one more question: how do I add more fields from T1 to my output file?

                        Even though I load 3 fields from T1, only the 2 fields used for the intervalmatch appear on the .qvd file that I store?

                        I also need this field: [SETNAME] as [Account Group_SETNAME].

                         

                        Thank you for your help!!

                        Jeanine

                         

                        I've attached my code.

                         

                         

                         

                         

                         

                         

                  • Nested SQL select
                    Celambarasan Adhimulam

                    Hi,

                         You can try with this..

                        

                    Table1:

                    LOAD * Inline

                    [

                    SETNAME,VALFROM,VALTO

                    Wages,1000,1004

                    Tea,1005,1009

                    ];

                     

                     

                    Table2:

                    LOAD * Inline

                    [

                    KSTAR

                    1000

                    1001

                    1002

                    1003

                    1004

                    1005

                    1010

                    ];

                     

                     

                    Left Join(Table2)

                    IntervalMatch(KSTAR)

                    LOAD

                              VALFROM,

                              VALTO

                    Resident

                              Table1;

                     

                     

                    Left Join(Table2)

                    LOAD

                              VALFROM,

                              VALTO,

                              SETNAME

                    Resident

                              Table1;

                    DROP Table Table1;

                    DROP Fields VALTO, VALFROM;

                     

                    Celambarasan