Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Hello,
Maybe works if you use a time stamp field or something like incremental Load.
Attach a file.
Best regards.
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;
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.
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.