Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
@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)';
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
@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
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
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
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
Can you post your QVW.
Yes sure , here you have it