topic Re: Calling table from a QVD into a script in QlikView

Calling table from a QVD into a script

klaus_feldam — Tue, 10 Apr 2012 02:32:04 GMT

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

fosuzuki — Tue, 10 Apr 2012 03:09:30 GMT

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 — Tue, 10 Apr 2012 12:19:19 GMT

@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 — Tue, 10 Apr 2012 12:25:02 GMT

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 — Wed, 11 Apr 2012 03:09:03 GMT

@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

adhudson — Wed, 11 Apr 2012 03:22:23 GMT

Hi,

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

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

Re: Calling table from a QVD into a script

Miguel_Angel_Baeyens — Wed, 11 Apr 2012 08:58:23 GMT

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

Calling table from a QVD into a script

Wed, 09 May 2012 12:04:48 GMT

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

Wed, 09 May 2012 12:24:14 GMT

Can you post your QVW.

Re: Calling table from a QVD into a script

Wed, 09 May 2012 12:28:12 GMT

Yes sure , here you have it

Re: Calling table from a QVD into a script

Thu, 10 May 2012 11:01:30 GMT

FInd the attached updated App. It should work now.

Re: Calling table from a QVD into a script

Thu, 10 May 2012 11:06:50 GMT

Thank's man

It is working but i still don't understand why it wasn't working when i did it:)

Can you explain it to me?

Re: Calling table from a QVD into a script

Thu, 10 May 2012 11:57:18 GMT

If you see the code, I have changed the structure. When the structure of a table is same and if you want to create an other table with the same structure then you can add some junk field and achieve and drop the field later.

Re: Calling table from a QVD into a script

Thu, 10 May 2012 12:10:52 GMT

i understand your structure.

But i dont understand why code which i have wrote didnt work.

Is it because i wanted to store table which was linked to sources from files ?

Or because of something different?

...