13 Replies Latest reply: May 10, 2012 8:10 AM by jejkujejku RSS

    Calling table from a QVD into a script

    Klaus Feldam

      I am running into issues calling a table (SalesDetails) from a QVD.

       

      Contents of QVD:

      -------------------------

       

      SalesDetails:
      LOAD
      SHDCTO & '_' & SHDOCO & '_' & SHKCOO as SalesHeaderKey,  // Link Header to Detail
      SHKCOO As [Order Company],

      SHVR01 as [PO Number],
      SHVR02 as [Lock Box Info],
      SHCARS as [Check Number],
      SHCNID as [Ordered By],
      SHTRDC as [Trade Discount],
      SHTKBY as [Order Taken By],
      SHDEL1 as [Delivery Instructions 1],
      SHDEL2 as [Delivery Instructions 2]
      ;
      SQL SELECT *
      FROM F4201
      ;

      STORE SalesDetails INTO All_4201.qvd (qvd);

       

      -------------------

       

      When I run the following script, where I trying to call the SalesHeader table, I am getting an error, saying that the resident table can't be found.

      Could anyone cast a quick look and see where I am going wrong?

       

       

      LOAD *

      FROM D:\Client View\AllClients\All_4201.qvd (qvd)

      where [Order Company]= '$(CompanyLimit)';

       

      YTD:

      Load

      YTDKey,

      Sum(if(EXISTS([Current YTD Date], Date), [Net Sales Amount])) as [YTD Net Sales Amount], 

      Sum(if(EXISTS([Current YTD Date], Date), [Net Sales Quantity])) as [YTD Net Sales Quantity],

      Sum(if(EXISTS([Current YTD Date], Date), [Sales Margin Amount])) as [YTD Sales Margin Amount],

      Sum(if(EXISTS([Current YTD Date], Date), [Sales Cost Amount])) as [YTD Sales Cost Amount],

      RESIDENT SalesDetails

      WHERE EXISTS([YTD Date], Date)

      GROUP BY YTDKey;

        • Calling table from a QVD into a script
          Fernando Suzuki

          I guess that the table name us missing:

           

          SalesDetail:

          LOAD *

          FROM D:\Client View\AllClients\All_4201.qvd (qvd)

          where [Order Company]= '$(CompanyLimit)';

           

          hope this helps you

          Fernando

            • Calling table from a QVD into a script
              Klaus Feldam

              @Fernando: Thanks for your suggestion. Unfortunately, adding the table name SalesDetails (as below) brings up the same script error - missing table.

               

              SalesDetails:

              LOAD *

              FROM D:\Client View\AllClients\All_4201.qvd (qvd)

              where [Order Company]= '$(CompanyLimit)';

                • Re: Calling table from a QVD into a script
                  Miguel Angel Baeyens de Arce

                  Hi Klaus,

                   

                  It seems that you are loading the same table structure twice, the first using the SalesDetails label and doing the STORE, then the LOAD * not using the label.

                   

                  If that's the case, the thing to note is that QlikView concatenates automatically two or more tables when they have the same number of fields and all these fields are named alike.

                   

                  So probably your script work if written like:

                   

                  SalesDetails:
                  LOAD
                  SHDCTO & '_' & SHDOCO & '_' & SHKCOO as SalesHeaderKey,  // Link Header to Detail
                  SHKCOO As [Order Company],
                  SHVR01 as [PO Number],
                  SHVR02 as [Lock Box Info],
                  SHCARS as [Check Number],
                  SHCNID as [Ordered By],
                  SHTRDC as [Trade Discount],
                  SHTKBY as [Order Taken By],
                  SHDEL1 as [Delivery Instructions 1],
                  SHDEL2 as [Delivery Instructions 2]
                  ;
                  SQL SELECT *
                  FROM F4201
                  ;
                  STORE SalesDetails INTO All_4201.qvd (qvd);
                  
                  [...] // more script here if any 
                  
                  SalesDetails2:
                  NOCONCATENATE LOAD * // Note the NOCONCATENATE
                  FROM D:\Client View\AllClients\All_4201.qvd (qvd)
                  where [Order Company]= '$(CompanyLimit)';
                  
                  YTD:
                  Load
                  YTDKey,
                  Sum(if(EXISTS([Current YTD Date], Date), [Net Sales Amount])) as [YTD Net Sales Amount], 
                  Sum(if(EXISTS([Current YTD Date], Date), [Net Sales Quantity])) as [YTD Net Sales Quantity],
                  Sum(if(EXISTS([Current YTD Date], Date), [Sales Margin Amount])) as [YTD Sales Margin Amount],
                  Sum(if(EXISTS([Current YTD Date], Date), [Sales Cost Amount])) as [YTD Sales Cost Amount],
                  RESIDENT SalesDetails2 // Note the 2
                  WHERE EXISTS([YTD Date], Date)
                  GROUP BY YTDKey;
                  

                   

                  Note that the table labelled as "SalesDetails2" is preceded by the NOCONCATENATE clause, that will prevent this automatic concatenation to take place. Note as well that the RESIDENT load is done on this "SalesDetails2" table instead of the SalesDetails.

                   

                  Hope that helps.

                   

                  Miguel

                    • Calling table from a QVD into a script
                      Klaus Feldam

                      @Miguel: Thanks for taking your time to respond.

                      I have tried applying your suggestions, but the script is unfortunately stalling and I eventually have to kill the reload. Removing NOCONCATENATE lets the script run, but with the following error message, which tells me that the following reference to SalesDetails2 doesn't work with a QVD file. Any further advise would be greatly appreciated.

                       

                       

                       

                      SalesDetails2:

                      LOAD *

                      FROM D:\Client View\AllClients\All_4211.qvd (qvd)

                      where [Order Company]= '$(CompanyLimit)';

                       

                      ------

                       

                      Table not found

                      YTD:

                      Load

                      YTDKey,

                      Sum(if(EXISTS([Current YTD Date], Date), [Net Sales Amount])) as [YTD Net Sales Amount], 

                      Sum(if(EXISTS([Current YTD Date], Date), [Net Sales Quantity])) as [YTD Net Sales Quantity],

                      Sum(if(EXISTS([Current YTD Date], Date), [Sales Margin Amount])) as [YTD Sales Margin Amount],

                      Sum(if(EXISTS([Current YTD Date], Date), [Sales Cost Amount])) as [YTD Sales Cost Amount],

                      RESIDENT SalesDetails2

                      WHERE EXISTS([YTD Date], Date)

                      GROUP BY YTDKey

                        • Re: Calling table from a QVD into a script
                          Miguel Angel Baeyens de Arce

                          Hi Klaus,

                           

                          If your script dies at the end, it's likely because of the unwanted creation of a huge synthetic table linking the original SalesDetails table with the SalesDetails2 table. There is a quick workaroud that I didn't add in my code, that is adding the following line:

                           

                          SalesDetails:
                          LOAD
                          SHDCTO & '_' & SHDOCO & '_' & SHKCOO as SalesHeaderKey,  // Link Header to Detail
                          SHKCOO As [Order Company],
                          SHVR01 as [PO Number],
                          SHVR02 as [Lock Box Info],
                          SHCARS as [Check Number],
                          SHCNID as [Ordered By],
                          SHTRDC as [Trade Discount],
                          SHTKBY as [Order Taken By],
                          SHDEL1 as [Delivery Instructions 1],
                          SHDEL2 as [Delivery Instructions 2]
                          ;
                          SQL SELECT *
                          FROM F4201
                          ;
                          
                          STORE SalesDetails INTO All_4201.qvd (qvd);
                          
                          [...] // more script here if any 
                          
                          SalesDetails2:
                          NOCONCATENATE LOAD * // Note the NOCONCATENATE
                          FROM D:\Client View\AllClients\All_4201.qvd (qvd)
                          where [Order Company]= '$(CompanyLimit)';
                          
                          YTD:
                          Load
                          YTDKey,
                          Sum(if(EXISTS([Current YTD Date], Date), [Net Sales Amount])) as [YTD Net Sales Amount], 
                          Sum(if(EXISTS([Current YTD Date], Date), [Net Sales Quantity])) as [YTD Net Sales Quantity],
                          Sum(if(EXISTS([Current YTD Date], Date), [Sales Margin Amount])) as [YTD Sales Margin Amount],
                          Sum(if(EXISTS([Current YTD Date], Date), [Sales Cost Amount])) as [YTD Sales Cost Amount],
                          RESIDENT SalesDetails2 // Note the 2
                          WHERE EXISTS([YTD Date], Date)
                          GROUP BY YTDKey;
                          
                          DROP TABLE SalesDetails2; // This is the new line that removes from memory the table, leaving the YTD table
                          

                           

                          Hope that helps.

                           

                          Miguel

                  • Re: Calling table from a QVD into a script

                    Hi,

                     

                        

                     

                    SalesDetails:
                    LOAD
                    SHDCTO & '_' & SHDOCO & '_' & SHKCOO as SalesHeaderKey,  // Link Header to Detail
                    SHKCOO As [Order Company],

                    SHVR01 as [PO Number],
                    SHVR02 as [Lock Box Info],
                    SHCARS as [Check Number],
                    SHCNID as [Ordered By],
                    SHTRDC as [Trade Discount],
                    SHTKBY as [Order Taken By],
                    SHDEL1 as [Delivery Instructions 1],
                    SHDEL2 as [Delivery Instructions 2]
                    ;
                    SQL SELECT *
                    FROM F4201
                    ;

                    STORE SalesDetails INTO All_4201.qvd (qvd);

                     

                    DROP Table SalesDetails; //DROP the table to avoid merging with the next load.

                     

                    SalesDetails: //Name the new table load as SalesDetails.

                    LOAD *

                    FROM D:\Client View\AllClients\All_4201.qvd (qvd)

                    where [Order Company]= '$(CompanyLimit)';

                     

                    YTD:

                    Load

                    YTDKey,

                    Sum(if(EXISTS([Current YTD Date], Date), [Net Sales Amount])) as [YTD Net Sales Amount], 

                    Sum(if(EXISTS([Current YTD Date], Date), [Net Sales Quantity])) as [YTD Net Sales Quantity],

                    Sum(if(EXISTS([Current YTD Date], Date), [Sales Margin Amount])) as [YTD Sales Margin Amount],

                    Sum(if(EXISTS([Current YTD Date], Date), [Sales Cost Amount])) as [YTD Sales Cost Amount],

                    RESIDENT SalesDetails

                    WHERE EXISTS([YTD Date], Date)

                    GROUP BY YTDKey;

                     

                    Hope this helps.

                     

                    Regards

                    Andrew Hudson

                    • Calling table from a QVD into a script

                      Hi i have same problem and i dont understand how to fix it

                      Please look for my script :

                      binary orange_koszty_april2012.qvw;

                      SET ThousandSep=' ';

                      SET DecimalSep='.';

                      SET MoneyThousandSep=' ';

                      SET MoneyDecimalSep='.';

                      SET MoneyFormat='# ##0.00 zł;-# ##0.00 zł';

                      SET TimeFormat='hh:mm:ss';

                      SET DateFormat='YYYY-MM-DD';

                      SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

                      SET MonthNames='sty;lut;mar;kwi;maj;cze;lip;sie;wrz;paź;lis;gru';

                      SET DayNames='Pn;Wt;Śr;Cz;Pt;So;N';

                      SET Directory=C:\Users\silarow\Desktop\qv;

                      SET NullInterpret='POZA';

                       

                       

                      LISTA:

                      Directory;

                      LOAD [Nr telefonu] as [Nr Tel],

                           MPK

                      FROM

                      orange_test_mpk.xlsx

                      (ooxml, embedded labels, table is [Dane dla TRINITY]);

                       

                       

                      LISTA2:

                      Directory;

                      LOAD [Nr telefonu] as [numer do],

                           MPK,

                           opis

                      FROM

                      orange_test_mpk.xlsx

                      (ooxml, embedded labels, table is [Dane dla TRINITY]);

                       

                       

                      Koszty:

                      inner join (LISTA) buffer LOAD [numer do],

                          [Nr Tel],

                           Typ,

                           Data,

                           godzina,

                           Sieć,

                           impulsy,

                           [koszt jedn],

                           [koszt jedn1],

                           [koszt netto],

                           [koszt burtto]

                      FROM

                      komórki\Book1.xlsx (ooxml, embedded labels);

                       

                       

                      inner join (LISTA2) buffer LOAD [Nr Tel],

                           Typ,

                           Data,

                           godzina,

                           Sieć,

                           [numer do],

                           impulsy,

                           [koszt jedn],

                           [koszt jedn1],

                           [koszt netto],

                           [koszt burtto]

                      FROM

                      komórki\Book1.xlsx (ooxml, embedded labels)

                       

                       

                      ;

                       

                       

                      STORE Koszty INTO 1x.qvd;

                       

                      Error message is :

                      Table not found

                      STORE Koszty INTO 1x.qvd

                       

                      Can any one help me ... i dont know what i did wrong

                      • Re: Calling table from a QVD into a script

                        Can you post your QVW.