Calling table from a QVD into a script - Qlik Community

klaus_feldam · ‎2012-04-09

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;

fosuzuki · ‎2012-04-09

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

klaus_feldam · ‎2012-04-10

@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)';

Miguel_Angel_Baeyens · ‎2012-04-10

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

klaus_feldam · ‎2012-04-10

@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

adhudson · ‎2012-04-10

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

Miguel_Angel_Baeyens · ‎2012-04-11

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

Report Inappropriate Content · ‎2012-05-09

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

Report Inappropriate Content · ‎2012-05-09

Can you post your QVW.

Report Inappropriate Content · ‎2012-05-09

Yes sure , here you have it