Joining Cross Table to lookup table + adding a filter

[#1]

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

[marcus_sommer]

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

[#3]

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

[marcus_sommer · Accepted solution]

You are missing the table-load within your statement:

inner join (YourTableToJoin)

Load * From YourSource

where match([Division],'Division1','Division2','Division3');

- Marcus

[#5]

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.