topic Re: Left join causing script load fail with no error message? in QlikView

Left join causing script load fail with no error message? Server vs. desktop?

erjohnso — Wed, 11 Dec 2013 21:04:22 GMT

Good afternoon all. In a simple data load, I am using a base data file then appending monthly data using Concatenate. In order to align all customers to master clients for historical trending, I also using a left join to add a crosswalk master client field where applicable. Here is an example data set, followed by the script:

Data tables:

Revenue:

CustomerName	CustomerID	ClientName	ClientID	SalesDollars	Month
Customer A	A1	Client X	X1	100	March 2011
Customer B	B1	Client Y	Y1	110	July 2012
Customer C	C1	Client X	X1	140	December 2012
Customer D	D1	Client Z	Z1	135	April 2013

Revenue Update1Q:

CustomerName	CustomerID	ClientName	ClientID	SalesDollars	Month
Customer A	A1	Client X	X1	75	July 2013
Customer B	B1	Client Y	Y1	120	July 2013
Customer C	C1	Client X	X1	150	August 2013
Customer D	D1	Client Z	Z1	130	September 2013

Revenue Update 2Q:

CustomerName	CustomerID	ClientName	ClientID	SalesDollars	Month
Customer A	A1	Client X	X1	120	October 2013
Customer B	B1	Client Y	Y1	150	November 2013
Customer C	C1	Client X	X1	90	December 2013
Customer D	D1	Client Z	Z1	100	December 2013

CustomerID	AdjustedClientID	AdjFlag
C1	Y1	Y
D1	X1	Y

The purpose of the script is to merge the tables into one and add the additional columns that I can reference in a future script with an IF statement. When I run the the debug on the following, it runs with no error but I get the message that script has failed. I commented out various steps of the script and everything works except the LEFT JOIN. What am I missing here??? Thank you!

SCRIPT:

NoConcatenate

Revenue:

LOAD

CustomerName,

CustomerID,

ClientName,

ClientID,

SalesDollars,

Month

FROM

[..\External Data\FY11-FY13.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

[RevenueUpdate1Q]:

Concatenate(Revenue)

LOAD

CustomerName,

CustomerID,

ClientName,

ClientID,

SalesDollars,

Month

FROM

[..\External Data\1QFY14.xlsx]

(ooxml, embedded labels, table is ALL);

[RevenueUpdate2Q]:

Concatenate(Revenue)

LOAD

CustomerName,

CustomerID,

ClientName,

ClientID,

SalesDollars,

Month

FROM

[..\External Data\Oct-Nov FY2014 by Entity.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Revenue)

Adj:

LOAD

CustomerID,

AdjustedClientID,

AdjustFlag

FROM

[..\External Data\Adjustments 120213.xlsx]

(ooxml, embedded labels, table is [Adjustments]);

STORE [Revenue] into ..\QVD\FYRevenue.qvd;

Re: Left join causing script load fail with no error message?

Wed, 11 Dec 2013 21:10:37 GMT

Hello,

Maybe works if you use a time stamp field or something like incremental Load.

Attach a file.

Best regards.

Re: Left join causing script load fail with no error message?

erjohnso — Wed, 11 Dec 2013 21:33:26 GMT

Thanks. It has nothing to do with the concatenate... if I just use the base file and the left join, I get the script fail with no error message.

Revenue:

CustomerName	CustomerID	ClientName	ClientID	SalesDollars	Month
Customer A	A1	Client X	X1	100	March 2011
Customer B	B1	Client Y	Y1	110	July 2012
Customer C	C1	Client X	X1	140	December 2012
Customer D	D1	Client Z	Z1	135	April 2013

Adj:

CustomerID	AdjustedClientID	AdjFlag
C1	Y1	Y
D1	X1	Y

SCRIPT:

Revenue:

LOAD

CustomerName,

CustomerID,

ClientName,

ClientID,

SalesDollars,

Month

FROM

[..\External Data\FY11-FY13.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

Left Join(Revenue)

Adj:

LOAD

CustomerID,

AdjustedClientID,

AdjustFlag

FROM

[..\External Data\Adjustments 120213.xlsx]

(ooxml, embedded labels, table is [Adjustments]);

STORE [Revenue] into ..\QVD\FYRevenue.qvd;

Re: Left join causing script load fail with no error message?

erjohnso — Mon, 06 Jan 2014 20:01:28 GMT

Interestingly enough... the issue, though unresolved, has nothing to do with the code. When I run this script through the server version of QV, the script fails. When I run using my desktop version, the application functions as expected.

Re: Left join causing script load fail with no error message? Server vs. desktop?

jpapador — Mon, 06 Jan 2014 20:13:16 GMT

Do you have access to write the QVD to that directory. Also sometimes QV keeps files open, try deleting FYRevenue.qvd if it already exists and then try running the script.