19 Replies Latest reply: Mar 21, 2016 5:34 AM by jagan mohan rao appala RSS

    qvd

    Prasanta Kumar

      Hi,

       

      I have two qvd files and want to combine them to get the required data from both tables with a where clause in second qvd file.

       

      Where clause will be "where [AccountNumber] = 'xyz'

       

      can you please help me with the data load script.

       

      thanks in advance

       

      [Fact]:

      LOAD [CountryName]

            ,[Geography]

            ,[AccountId]

      FROM [lib://123/Fact.qvd]

      (qvd);

       

      [Account]:

      LOAD [AccountId],

        [AccountNumber],

        [AccountName],

      FROM [lib://123/Account.qvd]

      (qvd);

        • Re: qvd
          Onno van Knotsenburg

          [Account]:

          LOAD [AccountId],

            [AccountNumber],

            [AccountName],

          FROM [lib://123/Account.qvd] (qvd)

          where [AccountNumber] = 'xyz';

          • Re: qvd
            Sangram Reddy

            Hi Prasanta,

             

            You can use this as well:

             

            load * inline [

            Needed

            XYZ

            ];

             

            [Account]:

            LOAD [AccountId],

              [AccountNumber],

              [AccountName],

            FROM [lib://123/Account.qvd] (qvd)

            where exists([Needed],[AccountNumber]);

             

            If you need multiple values in the where clause, you can just add it to the inline table and they all will be included in the where clause. (Similar to an "IN" function in SQL)

             

            Thanks,

            Sangram.

            • Re: qvd
              jagan mohan rao appala

              HI,

               

              Try like this

               

              [Account]:

              LOAD [AccountId],

                [AccountNumber],

                [AccountName],

              FROM [lib://123/Account.qvd]

              (qvd);

               

              LEFT JOIN(Account)

              LOAD [CountryName]

                    ,[Geography]

                    ,[AccountId]

              FROM [lib://123/Fact.qvd]

              (qvd);

               

              Regards,

              Jagan.

              • Re: qvd
                Prasanta Kumar

                Hello Everyone,

                 

                May be my question is not that clear.

                 

                actually i have multiple qvd files and the requirement as below.

                 

                In my main table (Table A), i have 11 lacs data in which i have my accountID based on which i will map and get the required data from other tables.

                 

                In table B, i have account name and account desc.

                In table C, i have account location.

                 

                So the requirement is when account name is "XYZ" in table B, i want the related rows from table A, B and C out of 11 lacs total rows to be loaded into my app rather loading all 11 lacs rows.

                 

                this is something i have to join qvd files where account name is XYZ in table B.

                reddys310

                 

                Regards,

                Prasanta

                • Re: qvd
                  kushal chawda

                  try this

                   

                  [Account]:

                  LOAD [AccountId],

                    [AccountNumber],

                    [AccountName],

                  FROM [lib://123/Account.qvd]

                  (qvd)

                  where [AccountNumber]='xyz';

                   

                  [Fact]:

                  LOAD [CountryName]

                        ,[Geography]

                        ,[AccountId]

                  FROM [lib://123/Fact.qvd]

                  (qvd)

                  where exists ([AccountId]);

                   

                  Qlik will automatically create the join on [AccountId].

                    • Re: qvd
                      Prasanta Kumar

                      Hi

                      Its seems like working.

                       

                      but what will happen in case i reverse the order to table. as mentioned i have multiple table to add.

                      will it as below :

                       

                      [Fact]:

                      LOAD [CountryName]

                            ,[Geography]

                            ,[AccountId]

                      FROM [lib://123/Fact.qvd]

                      (qvd)

                      where exists ([AccountId]);

                       

                      [Account]:

                      LOAD [AccountId],

                        [AccountNumber],

                        [AccountName],

                      FROM [lib://123/Account.qvd]

                      (qvd)

                      where [AccountNumber]='xyz';

                        • Re: qvd
                          Avinash R

                          Order is most important here...

                           

                          which ever the table you load fist will be taken as the reference , in the above post you have loaded Fact as the first table and if [AccountId] is loaded earlier than that column will be taken as the reference for load..if the [AccountId] is not loaded previously then it will have no effect it will not have any data reduction 

                      • Re: qvd
                        Avinash R

                        Hi Prasanta,

                         

                        Try like this

                        [Fact]:

                        LOAD [CountryName]

                              ,[Geography]

                              ,[AccountId]

                        FROM [lib://123/Fact.qvd]

                        (qvd);

                         

                        Join([Fact])

                        [Account]:

                        LOAD [AccountId],

                          [AccountNumber],

                          [AccountName],

                        FROM [lib://123/Account.qvd]

                        (qvd)

                        where [AccountNumber] = 'xyz';

                         

                        If want to join 2 tables and have it as a single table then use the above code .....if you don't you join link will automatically link both the tables with the common key between them