Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to join a lookup table to a cross filter table and filter the results in the load statement instead of within each Qlik chart.
I keep getting a garbage found error when I try to add the following code after the "Directory:" section, I think I am putting this code in the wrong place or my syntax is wrong.
"
inner join Hierarchy on Hierarchy.[GL Cost Center Number] = DataTemp.[GL Cost Center Number]
where match([Division],'Division1','Division2','Division3');
"
This is my load statement, which loads fine and tests okay. The hierarchy is the lookup, then it loads the cross table data, then it turns the cross table data into a date format, then it loads the data that is not in cross table format.
Hierarchy:
LOAD
num([GL Cost Center Number],'####') as [GL Cost Center Number],
[Region],
[Division],
[StatusFlag],
[BranchType],
[RegionManager],
[CenterManager],
[DivisionManager],
[GroupName],
[Cost Center Description]
FROM
(ooxml, embedded labels, table is [Sheet1]);
DataTemp:
CrossTable(EffectiveDate, Value, 6)
LOAD PrcsDate,
/// Date(Date#(PrcsDate,'DD/MM/YYYY'),'DD/MM/YYY') as PrcsDate ,
// Date(Date#(PrcsDate, 'MM/DD/YYYY')) as PrcsDate,
// date(Date#(PrcsDate),'MM/DD/YYYY') as PrcsDate,
RunWhen,
num([RollCenter],'####') as [GL Cost Center Number],
CtrDesc,
Cat1,
Desc,
PJan as "1/1/2015" ,
PFeb as "2/1/2015",
PMar as "3/1/2015",
PApr as "4/1/2015" ,
PMay as "5/1/2015",
PJun as "6/1/2015",
PJul as "7/1/2015",
PAug as "8/1/2015",
PSep as "9/1/2015",
POct as "10/1/2015",
PNov as "11/1/2015",
PDec as "12/1/2015",
CJan as "1/1/2016",
CFeb as "2/1/2016",
CMar as "3/1/2016",
CApr as "4/1/2016",
CMay as "5/1/2016",
CJun as "6/1/2016",
CJul as "7/1/2016",
CAug as "8/1/2016",
CSep as "9/1/2016",
COct as "10/1/2016",
CNov as "11/1/2016",
CDec as "12/1/2016"
FROM
Qlickview_SVAData_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
;
Store DataTemp into Temp.qvd (qvd);
Drop Table DataTemp;
MonthlyData:
Load //Date(Date#(PrcsDate, 'MM/DD/YYYY')) as PrcsDate,
RunWhen,
[GL Cost Center Number],
CtrDesc,
Cat1,
Desc,
Date(Date#(EffectiveDate, 'MM/DD/YYYY')) as EffectiveDate,
Value
From Temp.qvd (qvd);
ToDateData:
LOAD //Date(Date#(PrcsDate, 'MM/DD/YYYY')) as PrcsDate,
date(floor(timestamp#(PrcsDate, 'MM/DD/YYYY hh:mm:ss'))) as PrcsDate,
// PrcsDate,
RunWhen,
num([RollCenter],'####') as [GL Cost Center Number],
CtrDesc,
Cat1,
Desc,
CurrYrMo,
LastYrMo,
CurrBudMo,
DiffMo,
CurrYTD,
CurrBudYTD,
PriorYTD,
Mo_BudVar,
YOYChg,
YTD_BudVar
FROM
Qlickview_SVAData_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
You are missing the table-load within your statement:
inner join (YourTableToJoin)
Load * From YourSource
where match([Division],'Division1','Division2','Division3');
- Marcus
The syntax for joins in qlikview is a bit different to sql. You couldn't specify fields which should be used as keys for the join instead of them all fields withe the same fieldname will be automatically used for the joining. This meant your join-statement should more look like:
inner join (YourTableToJoin)
Load * From YourSource
where match([Division],'Division1','Division2','Division3');
- Marcus
Thank you for your reply Marcus. Duly noted that I need to omit the join fields, which has been done, however I am still getting the garbage notification when I update my script. Am I inputting the join criteria in the wrong place? Also, the Division field exists in the Hierarchy table, but not in the Datatemp table that I am creating, I suspect that could be part of the issue:
See the preceding script text in the datatemp section from my original post
CNov as "11/1/2016",
CDec as "12/1/2016"
FROM
Qlickview_SVAData_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Then I appended this right after:
inner join (Hierarchy)
where match([Division],'Division1','Division2','Division3');
You are missing the table-load within your statement:
inner join (YourTableToJoin)
Load * From YourSource
where match([Division],'Division1','Division2','Division3');
- Marcus
Thanks Marcus. You set me on the right track. I figured out that i needed to have a semi colon before my inner join statement.