18 Replies Latest reply: Oct 5, 2016 4:21 PM by Court van de Lisdonk RSS

    combine three tables

    Court van de Lisdonk

      Hello,

       

      I have a Problem perhaps for you a very little one to solve. Let me explain: there are three tables:

       

      Temp_Include:
      NoConcatenate  LOAD
      including_Company
      FROM [$(DATADIR)\Excel\Umsatzbildung001.xlsx]
      (ooxml, embedded labels, table is including_Companys)
      WHERE (Active)=1;

       

      Temp_Customers:
      NoConcatenate LOAD
      AX_SellToCustomerNumber
      //     AX_Active
      FROM [$(DATADIR)Excel\Umsatzbildung001.xlsx]
      (ooxml, embedded labels, table is excluding_Customers)
      WHERE (AX_Active)=1;

      and

       

      HstDat_SalesInvoice:
      LOAD

      Company &’’& DataLevel as CompanyKey

      Company,
      DataLevel,
      LoadingDate,
      CustomerID

      FROM [$(DATADIR)qvd\Sales.qvd] (qvd);

       

      I want to make one table out of three. There should be one table HstDat_SalesInvoice, where  company is equal including_company from table Temp_Include and CustomerID not equal AX_SellToCustomerNumber from Temp_Customers:

      I think it should be something like this:

      HstDat_SalesInvoice:

      LOAD

      Company &’’& DataLevel as CompanyKey
      Company,
      WHERE EXISTS (including_Company);
      LOAD       
      DataLevel,
      LoadingDate,

      CustomerID

      FROM [$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd)
      Where (CustomerID <> AX_SellToCustomerNumber);

      I got the  next messages when I reload:

      1. 1. Syntax error, missing/misplaced FROM: HstDat_SalesInvoice:
      2. 2. Field not found - <AX_SellToCustomerNumber>

       

      I hope someone knows how I can solve this,


      Thanks in advance


      regards


      Court van de Lisdonk

       

        • Re: combine three tables
          Anil Babu Samineni

          Try with Join / Concatenate

           

          What is the issue you are getting?

          • Re: combine three tables
            nagarjuna k

            As per the ur above script , your problem will not solve,

             

            How you want combine those three table without having any common fields .

             

            please share proper information , that suite ur requiremnt.

             

             

            -Nagarjun

              • Re: combine three tables
                Court van de Lisdonk

                The problem i have is that data from sales ist 'dirty' so different data

                should be excluded. For instance there are old company Numbers which should

                be excluded, the temp-table has the right companynumbers. For customers:

                the temp-table has Numbers from customers which don't exist and also be

                excluded!!! Regards

                Am Freitag, 30. September 2016 schrieb nagarjuna k :

                 

                 

                 

              • Re: combine three tables
                Rupam Das

                Maybe this(changes in bold). But a sample app might help

                 

                Temp_Include:

                NoConcatenate  LOAD

                including_Company

                FROM [$(DATADIR)\Excel\Umsatzbildung001.xlsx]

                (ooxml, embedded labels, table is including_Companys)

                WHERE (Active)=1;

                Temp_Customers:

                NoConcatenate LOAD

                AX_SellToCustomerNumber as CustomerID

                //     AX_Active

                FROM [$(DATADIR)Excel\Umsatzbildung001.xlsx]

                (ooxml, embedded labels, table is excluding_Customers)

                WHERE (AX_Active)=1;

                 

                HstDat_SalesInvoice:

                LOAD

                Company &’’& DataLevel as CompanyKey

                Company,

                WHERE EXISTS (including_Company);

                LOAD      

                DataLevel,

                LoadingDate,

                CustomerID

                 

                FROM [$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd)

                Where not exists (CustomerID);

                  • Re: combine three tables
                    Rupam Das

                    More refined:

                     

                    Temp_Include:

                    NoConcatenate  LOAD

                    including_Company as Company

                    FROM [$(DATADIR)\Excel\Umsatzbildung001.xlsx]

                    (ooxml, embedded labels, table is including_Companys)

                    WHERE (Active)=1;

                    Temp_Customers:

                    NoConcatenate LOAD

                    AX_SellToCustomerNumber as CustomerID

                    //     AX_Active

                    FROM [$(DATADIR)Excel\Umsatzbildung001.xlsx]

                    (ooxml, embedded labels, table is excluding_Customers)

                    WHERE (AX_Active)=1;

                     

                    HstDat_SalesInvoice:

                    LOAD

                    Company &’’& DataLevel as CompanyKey

                    Company,

                    WHERE EXISTS (Company);

                    LOAD      

                    DataLevel,

                    LoadingDate,

                    CustomerID

                     

                    FROM [$(DATADIR)qvd\HstDat_SalesInvoice.qvd] (qvd)

                    Where not exists (CustomerID);

                  • Re: combine three tables
                    Rajpreeth S

                    Hi..

                    You can try the following conditions to avoid those two errors..

                    1-„ÄčThe first where exists should be replaced with following condition.

                    WHERE EXISTS(INCLUDING_COMPANY,COMPANY)

                    2-„ÄčThe second where conditions can be changed as follows.

                    WHERE NOT EXISTS(AX_SellToCustomerNumber,Customer ID)

                     

                    Please try this and let me know if this works as per your requirement

                      • Re: combine three tables
                        Rajpreeth S

                        Please use field signatures as you used in your application

                          • Re: combine three tables
                            Rajpreeth S

                            Use the above conditions with this load script as below..

                             

                             

                            HstDat_SalesInvoice:
                            LOAD

                            Company &’’& DataLevel as CompanyKey

                            Company,
                            DataLevel,
                            LoadingDate,
                            CustomerID

                            FROM [$(DATADIR)qvd\Sales.qvd] (qvd)

                            WHERE EXISTS(including_Company,Company)

                            AND not EXISTS(AX_SellToCustomerNumber,Customer ID);

                            Try this and let me know..

                              • Re: combine three tables
                                Court van de Lisdonk

                                I will, thank you in advance

                                • Re: combine three tables
                                  Court van de Lisdonk

                                  Hello,

                                   

                                  Your Solution worked, thank you for that. Perhaps you can help me one more time. If I want to make an IF-statement during the load like this

                                  i

                                  f ((Company = $(including_Company))and (CustomerID <> $(AX_SellToCustomerNumber)), Year(Postingdate)) as Year

                                   

                                  I got a message:

                                  error in expression: )' expected

                                  HstDat_SalesInvoice:

                                  LOAD

                                  Company &' '& DataLevel      as DimHierPrimDimCompanyKey,

                                  Company,

                                       DataLevel,

                                       LoadingDate,

                                  PostingDate,

                                       CustomerID,

                                      if ((Company = )and (CustomerID <> ), Year(Postingdate)) as Year

                                  FROM

                                  [C:\QlikView Development\Test\Datasources\HstDat_SalesInvoice.qvd]

                                  (qvd)

                                   

                                  I thought I made it correct. Can it be that I cannot use $(including_Company) and $(AX_SellToCustomerNumber)?

                                   

                                  This is my LOAD-command/page is :

                                   

                                  Temp_Include:
                                  NoConcatenate LOAD
                                    including_Company
                                  FROM [D:\QlikView Development\Test\Datasources\Umsatzbildung001.xlsx]
                                  (ooxml, embedded labels, table is including_Companys)
                                  WHERE (Active)=1;

                                   

                                  Temp_Customers:
                                  // Left Join(LimitedTable)
                                  NoConcatenate LOAD
                                     AX_SellToCustomerNumber
                                  FROM [D:\QlikView Development\Test\Datasources\Umsatzbildung001.xlsx]
                                  (ooxml, embedded labels, table is excluding_Customers)
                                  WHERE (AX_Active)=1;

                                   

                                  HstDat_SalesInvoice:

                                  LOAD

                                  Company &' '& DataLevel      as DimHierPrimDimCompanyKey,

                                  Company,

                                       DataLevel,

                                       LoadingDate,

                                  PostingDate,

                                       CustomerID,

                                      if ((Company = $(including_Company))and (CustomerID <> $(AX_SellToCustomerNumber)), Year(Postingdate)) as Year

                                  FROM

                                  [C:\QlikView Development\Test\Datasources\HstDat_SalesInvoice.qvd]

                                  (qvd);

                                   

                                  Thanks in advance

                                    • Re: combine three tables
                                      Anil Babu Samineni

                                      Instead of that you can use this if those Two are fields

                                       

                                      if (Company = including_Company and CustomerID <> AX_SellToCustomerNumber, Year(Postingdate)) as Year


                                      Does it make sense, or else let me know what is your intend?

                                        • Re: combine three tables
                                          Court van de Lisdonk

                                          Hello,

                                           

                                          I have changed the loading statement, but now i got the message Field not

                                          found -< including_Company>

                                          When I make a where-statement at the end (after [C:\QlikView

                                          Development\Test\Datasources\HstDat_SalesInvoice.qvd]

                                          (qvd)) I don't get a message and the field is found. Do I miss

                                          something????? Is the place of the IF-statement wrong, or should I map the

                                          table with the including_Company field?

                                           

                                          The scripteditor give no signs that something is wrong

                                           

                                           

                                          HstDat_SalesInvoice:

                                          LOAD

                                                      Company &' '& DataLevel

                                                                                             as

                                          DimHierPrimDimCompanyKey,

                                                      Company,

                                                      DataLevel,

                                               LoadingDate,

                                                      PostingDate,

                                               CustomerID,

                                                  if  ((Company = including_Company ) and (CustomerID<>

                                          AX_SellToCustomerNumber), Year(PostingDate)) As LimitedPostingDate

                                             FROM

                                           

                                          (qvd)

                                          ;

                                           

                                           

                                           

                                           

                                          Field not found - AX_ItemNumber ),

                                          LineAmount, NULL()) As LimitedLineAmount

                                           

                                          FROM

                                           

                                           

                                           

                                          (qvd)

                                           

                                          With regards

                                           

                                           

                                           

                                          Court van de Lisdonk

                                           

                                           

                                           

                                          Email: courtvdlisdonk@gmail.com

                                           

                                           

                                           

                                           

                                           

                                          2016-10-03 18:35 GMT+02:00 Anil babu <qcwebmaster@qlikview.com>:

                                           

                                           

                                           

                                • Re: combine three tables
                                  Court van de Lisdonk

                                   

                                  Hello,

                                   

                                  The reason I want Year from PostingDate as  LimitedPostingDate is that I needed further in the script to make a kind of select load. Perhaps the name LimitedPostingDate is wrong and i should have named the field LimitedYear, this year is only wanted when Company = including_Company and CustomerID <> X_SellToCustomerNumber), normally   LimitedPostingDate or LimitedYear is this Year: My script has variable vYear which is 2016, in the maintab I have a SET vYear = year(date(now()))

                                   

                                  Including_Company are companynumbers which are old and not longer used AX_SellToCustomerNumbers are numbers from another, old system and also no longer in use.

                                   

                                  Hope you see the Logic now and can help me solving my problem

                                   

                                  hanks in advance

                                   

                                   

                                    • Re: combine three tables
                                      Rajpreeth S

                                      Please find the answers in inline

                                       

                                      Hello,

                                       

                                      I have changed the loading statement, but now i got the message Field not

                                      found -< including_Company>

                                      When I make a where-statement at the end (after [C:\QlikView

                                      Development\Test\Datasources\HstDat_SalesInvoice.qvd]

                                      (qvd)) I don't get a message and the field is found. Do I miss

                                      something?????

                                       

                                      Yes.you will get a message such if a field called 'including_Company'  is not avaliable in your  HstDat_SalesInvoice.qvd

                                       

                                      Is the place of the IF-statement wrong, or should I map the

                                      table with the including_Company field?

                                       

                                      You can left join the two tables if needed -- > It depends on your data model design approach

                                       

                                      The scripteditor give no signs that something is wrong

                                       

                                       

                                      HstDat_SalesInvoice:

                                      LOAD

                                                  Company &' '& DataLevel

                                                                                        as

                                      DimHierPrimDimCompanyKey,

                                                  Company,

                                                  DataLevel,

                                          LoadingDate,

                                                  PostingDate,

                                          CustomerID,

                                              if  ((Company = including_Company ) and (CustomerID<>

                                      AX_SellToCustomerNumber), Year(PostingDate)) As LimitedPostingDate

                                        FROM

                                       

                                      (qvd)

                                      ;

                                       

                                       

                                       

                                       

                                      Field not found - AX_ItemNumber ),

                                      LineAmount, NULL()) As LimitedLineAmount

                                       

                                      FROM

                                       

                                       

                                       

                                      (qvd)

                                       

                                      The above script can be modified as follows if you do not want  to join HstDat_SalesInvoice with a table which has including_Company  company field.(Temp_Include table)

                                       

                                       

                                       

                                      HstDat_SalesInvoice:
                                      LOAD

                                      Company &’’& DataLevel as CompanyKey

                                      Company,
                                      DataLevel,
                                      LoadingDate,
                                      CustomerID,

                                      Year(PostingDate)) As LimitedPostingDate

                                      FROM [$(DATADIR)qvd\Sales.qvd] (qvd)

                                      WHERE EXISTS(including_Company,Company)

                                      AND not EXISTS(AX_SellToCustomerNumber,Customer ID);


                                      Please share your application here for further queries.


                                      Rajpreeth S