11 Replies Latest reply: Sep 12, 2014 8:14 AM by Tobias Meyer RSS

    work with 2 sources

      Hi guys,

      I want to work with 2 Sources in Qlikview, well thats the strenght of QV I think, but I dont know, how to manage that:

       

      I have 2 sources like this:

       

      S1:

      KeyNr,

      LName,

      FName,

      BDate,

      Addressname,

      AddressNr,

      ...;

      SQL SELECT * FROM XDB;

      S2:

      NrKey,

      Name,

      FirstName,

      BDay,

      Adresse // (-> = Adressname+Nr)

      ...;

      SQL SELECT * FROM YDB;

       

      I have following data:

      in S1(XDB) are old data from 2010- June2014

      in S2(YDB) are newer data with active people from 2010 - today..

      ____

      I want to split this in 2 types/table: Person and Adress

       

      How can I realize that in Script ?

        • Re: work with 2 sources
          vinaykumar HG

          hi.. it would be helpful if you let us know, u want to concat and show all details or is it on demand data share of old and new data..

          • Re: work with 2 sources
            Ruben Marin

            You can concatenate both tables withalias so the fields will be the same:

             

            TempData:

            KeyNr,

            Addressname & AddressNr as Adresse

            ...;

             

            Concatenate (TempData)

            LOAD

            NrKey as KeyNr,

            ...;

            SQL SELECT * FROM YDB;

             

            Then you can divide data in 2 tables leaving Adresse or the field you want as link in both tables.

             

            Persons:

            LOAD Adresse,

                 LName...

            Resident TempData;

             

            Address:

            LOAD Adresse,

                 Addressname...

            Resident TempData;

             

            DROP Table TempData;

              • Re: work with 2 sources

                Right,

                But is it possible, to say that I, just want the data of S2(YDB) if it exist, if not- so from S1 ? Like Address,..

                Now it shows me 2 same lines (1 from S1 with A& B as Adress, 2. from S2)

                =)

                  • Re: work with 2 sources
                    Ruben Marin

                    Assuming that KeyNr can be used to check if data exists, you can use something like:

                     

                    TempData:

                    NrKey,

                    ...;

                    SQL SELECT * FROM YDB;

                     

                    S1:

                    KeyNr,

                    ...;

                    SQL SELECT * FROM XDB;

                     

                    Concatenate (TempData)

                    LOAD

                    KeyNr as NrKey,

                    ...

                    Resident S1 where not exists('NrKey', KeyNr);

                     

                    DROP Table S1;

                     

                    ...

                     

                    Edit: note that I change the order tables were read from SQL, so we load all records from S2 and only those not previously loaded from S1

                • Re: work with 2 sources

                  Well, after SQL.. FROM DB I cant use where not exists..

                    • Re: work with 2 sources
                      Ruben Marin

                      Check my previous post:

                       

                      TempData:

                      NrKey,

                      ...;

                      SQL SELECT * FROM YDB;

                       

                      S1:

                      KeyNr,

                      ...;

                      SQL SELECT * FROM XDB;

                       

                      Concatenate (TempData)

                      LOAD

                      KeyNr as NrKey,

                      ...

                      Resident S1 where not exists('NrKey', KeyNr);

                       

                      DROP Table S1;

                       

                      ...

                        • Re: work with 2 sources

                          Dont you mean drop table Temp?

                            • Re: work with 2 sources
                              Ruben Marin

                              That code was to be inserted before loading Person and Address tables, after loading these tables you can drop Temp table.

                               

                              S1 Table has to be dropped too, this can be done after concatenate S2 Table on Temp table or when Temp table is dropped.

                               

                              Full code will be:

                              TempData:

                              NrKey,

                              ...;

                              SQL SELECT * FROM YDB;

                               

                              S1:

                              KeyNr,

                              ...;

                              SQL SELECT * FROM XDB;

                               

                              Concatenate (TempData)

                              LOAD

                              KeyNr as NrKey,

                              ...

                              Resident S1 where not exists('NrKey', KeyNr);

                               

                              DROP Table S1;

                               

                              Persons:

                              LOAD Adresse,

                                   LName...

                              Resident TempData;

                               

                              Address:

                              LOAD Adresse,

                                   Addressname...

                              Resident TempData;

                               

                              DROP Table TempData;

                                • Re: work with 2 sources

                                  So all in all it looks like this:

                                   

                                  Temp_S1: //Add_data

                                  Load NrKey...

                                  SQL SELECT * FROM YDB

                                   

                                  S2: //Basic data

                                  Load KeyNr...

                                  SQL SELECT * FROM XDB

                                   

                                  Concatenate (Temp_S1)

                                  Load NrKey as Keynr...

                                  Resident Temp_S1 Where not exist('KeyNr', NrKey);

                                   

                                  Followed by

                                  Personal:

                                  Load Keynr, Name,...

                                  Resident S2;

                                   

                                  Address:

                                  Load Keynr, ...

                                  Resident S2;

                                   

                                   

                                  after all -> drop table Temp_S1 & S2

                                   

                                  Am I right?

                                    • Re: work with 2 sources
                                      Ruben Marin

                                      Almost, Personal and Address tables should read from Temp_S1 instead of S2.

                                       

                                      Temp_S1: //Add_data

                                      Load NrKey...

                                      SQL SELECT * FROM YDB

                                       

                                      S2: //Basic data

                                      Load KeyNr...

                                      SQL SELECT * FROM XDB

                                       

                                      Concatenate (Temp_S1)

                                      Load NrKey as Keynr...

                                      Resident S2 Where not exist('KeyNr', NrKey);

                                       

                                      Followed by

                                      Personal:

                                      Load Keynr, Name,...

                                      Resident Temp_S1;

                                       

                                      Address:

                                      Load Keynr, ...

                                      Resident Temp_S1;

                                       

                                      drop tables Temp_S1, S2;

                                    • Re: work with 2 sources

                                      Ok Thank you very much !