14 Replies Latest reply: Feb 5, 2016 7:14 PM by Marco Wedel RSS

    Create QVD

    reddy r

      T1:

         

      IDName
      1er
      2fsdf
      3dfsd

       

      T2:

       

         

      IDtext
      7nm
      8f
      9 j

       

      T3:

         

      IDCOM
      1cvd
      2df
      3v
      4sd
      5sdf
      6sdfa
      7fd
      8dffd
      9sfdf

      I have 3 tables  and a create QVD shold be created on below conditions:

      1)T3.ID = T1.ID

      2)T3.ID = T2.Id

      and the final Qvd will be created as below

        expected output:  

      IDCOMName text
      1cvder
      2dffsdf
      3vdfsd
      7fd fd
      8dffd dffd
      9sfdf sfdf

         

        • Re: Create QVD
          Gysbert Wassenaar

          T1:
          LOAD ID, Name FROM ...;

           

          JOIN

           

          LOAD ID, text FROM ...;

           

          JOIN

           

          LOAD ID, COM FROM ...;

           

          Result:

          LOAD * FROM T1

          WHERE Len(Trim()Name)> 0 or Len(Trim(text))>0;

           

          Drop Table T1;

            • Re: Create QVD
              reddy r

              Hi

               

              In my case T3 has 20million data so we cannot pull that many records as load time will be increased

              so i need to pull the data for T3  which will match from T1 and T2

              and we are not supposed to pull complete data of T3

                • Re: Create QVD
                  Gysbert Wassenaar

                  If you're not supposed to pull the complete data of T3 then I think you have no choice but to create an sql query that generates a result set on the database server that only contains the data you are allowed to pull. If you do the joining in Qlikview then you will need to pull all the data first before you can discard the records you don't need using joins and/or the Exists function.



              • Re: Create QVD
                Massimo Grossi

                T1:

                load * inline [

                ID Name

                1 er

                2 fsdf

                3 dfsd

                ] (delimiter is spaces);

                 

                join (T1)

                load * inline [

                ID text

                7 nm

                8 f

                9 j

                ] (delimiter is spaces);

                 

                left join (T1)

                load * inline [

                ID COM

                1 cvd

                2 df

                3 v

                4 sd

                5 sdf

                6 sdfa

                7 fd

                8 dffd

                9 sfdf

                ] (delimiter is spaces);

                 

                store T1 into T1.qvd (qvd);

                  • Re: Create QVD
                    reddy r

                    Hi

                     

                    In my case T3 has 20million data so we cannot pull that many records as load time will be increased

                    so i need to pull the data for T3  which will match from T1 and T2

                    and we are not supposed to pull complete data of T3

                      • Re: Create QVD
                        Graeme Smith

                        Result:
                        LOAD ID, Name

                        FROM T1.qvd (qvd);

                         

                        CONCATENATE (Result)

                        LOAD ID, text

                        FROM T2.qvd (qvd);

                         

                        JOIN (Result)

                        LOAD ID, COM

                        FROM T3.qvd (qvd)

                        WHERE EXISTS(ID, ID);

                         

                        Create QVD

                          • Re: Create QVD
                            reddy r


                            As per my requirement
                            Result:
                            LOAD ID, Name

                            FROM T1.qvd (qvd)
                            where Name = 'er';

                             

                            CONCATENATE (Result)

                            LOAD ID, text

                            FROM T2.qvd (qvd);

                             

                            JOIN (Result)

                            LOAD ID, COM

                            FROM T3.qvd (qvd)

                            WHERE EXISTS(ID, ID);

                            T1 and T3 are having 20 millions of data
                            So I need to pull the records based on above conditions
                            But  it is not working as expected.

                      • Re: Create QVD
                        Marco Wedel

                        Hi,

                         

                        nearly the same as the other solutions, but I used Exists(ID) like Graeme did to keep the load optimized and join the first tables rather than concatenating it to avoid duplicates with your real data:

                         

                         

                        QlikCommunity_Thread_204080_Pic1.JPG

                         

                        [expected output]:
                        LOAD * FROM [https://community.qlik.com/thread/204080] (html, codepage is 1252, embedded labels, table is @1);
                        Join
                        LOAD * FROM [https://community.qlik.com/thread/204080] (html, codepage is 1252, embedded labels, table is @2);
                        Join
                        LOAD * FROM [https://community.qlik.com/thread/204080] (html, codepage is 1252, embedded labels, table is @3)
                        Where Exists(ID);
                        
                        

                         

                        hope this helps

                         

                        regards

                         

                        Marco

                          • Re: Create QVD
                            Marco Wedel

                            some test script to compare the performance of the different solutions:

                             

                             

                            //-------------------------
                            //test data generation
                            //
                            //T1:
                            //LOAD RecNo() as ID,
                            //    Left(KeepChar(Hash256(Rand()),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),10) as Name
                            //AutoGenerate 100;
                            //STORE T1 into T1.qvd (qvd);
                            //
                            //T2:
                            //LOAD RecNo()+90 as ID,
                            //    Left(KeepChar(Hash256(Rand()),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),10) as text
                            //AutoGenerate 100;
                            //STORE T2 into T2.qvd (qvd);
                            //
                            //T3:
                            //LOAD Ceil(Rand()*10000000) as ID,
                            //    Left(KeepChar(Hash256(Rand()),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),10) as COM
                            //AutoGenerate 10000000;
                            //STORE T3 into T3.qvd (qvd);
                            //
                            //DROP Tables T1, T2, T3;
                            //-------------------------
                            
                            //-------------------------
                            //Solution 1
                            //
                            //temp:
                            //LOAD * FROM T1.qvd (qvd);
                            //Join
                            //LOAD * FROM T2.qvd (qvd);
                            //Join
                            //LOAD * FROM T3.qvd (qvd);
                            //
                            //NoConcatenate
                            //
                            //[expected output]:
                            //LOAD * Resident temp
                            //WHERE Len(Trim(Name))>0 or Len(Trim(text))>0;
                            //
                            //DROP Table temp;
                            //-------------------------
                            
                            //-------------------------
                            //Solution 2
                            //
                            //[expected output]:
                            //LOAD * FROM T1.qvd (qvd);
                            //Join
                            //LOAD * FROM T2.qvd (qvd);
                            //Left Join
                            //LOAD * FROM T3.qvd (qvd);
                            //-------------------------
                            
                            //-------------------------
                            //Solution 3
                            //
                            //[expected output]:
                            //LOAD * FROM T1.qvd (qvd);
                            //Join
                            //LOAD * FROM T2.qvd (qvd);
                            //Join
                            //LOAD * FROM T3.qvd (qvd)
                            //Where Exists(ID);
                            //-------------------------
                            

                             

                            Graeme's solution using the Exists() function seems to be the fastest by far.

                             

                            hope this helps

                             

                            regards

                             

                            Marco

                              • Re: Create QVD
                                Graeme Smith

                                From the sample data and requirements specified, there did not appear to be a need to handle duplicates across table one and two. As such, concatenating the tables will be much faster. If you need to handle duplicates across these files, then yes, you would have to join them, which would be much slower.

                                 

                                Regards,

                                 

                                Graeme

                              • Re: Create QVD
                                reddy r

                                As per my requirement

                                LOAD * FROM [https://community.qlik.com/thread/204080] (html, codepage is 1252, embedded labels, table is @1)

                                where Name = 'er'

                                Join 

                                04.LOAD * FROM [https://community.qlik.com/thread/204080] (html, codepage is 1252, embedded labels, table is @2); 

                                Join 

                                LOAD * FROM [https://community.qlik.com/thread/204080] (html, codepage is 1252, embedded labels, table is @3) 

                                Where Exists(ID);

                                T1 and T3 are having 20 millions of data
                                So I need to pull the records based on above conditions
                                But  it is not working as expected.

                              • Re: Create QVD
                                Nishit Oza

                                T1

                                load id,name

                                resident(normal file or in line table) t1;

                                 

                                T2

                                load id,name

                                resident t2;

                                 

                                 

                                 

                                T3

                                load *

                                resident t3

                                where Exists(id(//t1),id(//t3)) and Exists(id(t2),id(t3))

                                 

                                store t3 in te.qvd

                                • Re: Create QVD
                                  Marco Wedel

                                  please close some of your 12 open threads if you received correct answers.

                                   

                                  thanks

                                   

                                  regards

                                   

                                  Marco