Insert record in LinkTable where values match one of two Key fields
Hi everyone,
I'm not a QlikView Developer, but have taken over Administrative responsibilities of later. I've been struggling with this for a couple days, and figured it was time to stop sprinting backwards.
Our application uses a star schema with everything joining to a LinkTable. The LinkTable contains two Keys to dimension tables: Key|Reseller and Key|ReferralSystems.
All data is loaded from SQL tables into the LinkTable, and at the end of the script, we load data from an Excel spreadsheet containing Client and Goal information.
The IdClient in the spreadsheet can be associated with one of four options:
Single Reseller
Single ReferralSystem
Multiple Resellers
Multiple ReferralSystems
To simplify the logic, we can declare that if an IdReseller exists, we should use that and ignore the ReferralSystemId.
Additionally, to support the one-to-many relationship between a Client record and Resellers/ReferralSystems, we have some table records with GoalAmounts blank to avoid double-counting Goals in the application
An example of the Excel spreadsheet is below:
IdClient
Client
Date
IdReseller
ReferralSystemId
Goal
1
Client A
Jan2015
676
62
1
Client A
Feb2015
676
62
1
Client A
Mar2015
676
62
1
Client A
Apr2015
676
72
1
Client A
May2015
676
72
1
Client A
Jun2015
676
72
1
Client A
Jul2015
676
72
1
Client A
Aug2015
676
82
1
Client A
Sep2015
676
82
1
Client A
Oct2015
676
82
1
Client A
Nov2015
676
102
1
Client A
Dec2015
676
52
2
Client B
Jan2015
15
305
2
Client B
Feb2015
15
308
2
Client B
Mar2015
15
311
2
Client B
Apr2015
15
314
2
Client B
May2015
15
317
2
Client B
Jun2015
15
321
2
Client B
Jul2015
15
324
2
Client B
Aug2015
15
327
2
Client B
Sep2015
15
330
2
Client B
Oct2015
15
334
2
Client B
Nov2015
15
337
2
Client B
Dec2015
15
340
2
Client B
Jan2015
16
2
Client B
Feb2015
16
2
Client B
Mar2015
16
2
Client B
Apr2015
16
2
Client B
May2015
16
2
Client B
Jun2015
16
2
Client B
Jul2015
16
2
Client B
Aug2015
16
2
Client B
Sep2015
16
2
Client B
Oct2015
16
2
Client B
Nov2015
16
2
Client B
Dec2015
16
The Excel spreadsheet is being read twice into two separate different tables (ApptsBookedGoals for Client and Goal info and ClientLink as a temp table for all Reseller/ReferralSystem/Client associations):
ApptsBookedGoals:
LOAD Distinct
IdReseller & '_' & ReferralSystemId as Key|Client,
Client,
Date as GoalDate,
Goal as GoalAmount
FROM
[Data\Goal line for QV.xlsx]
(ooxml, embedded labels, table is Sheet1)
where len(trim(Goal)) > 0;
ClientLink:
LOAD Distinct
IdClient,
IdReseller as CL.IdReseller,
ReferralSystemId as CL.ReferralSystemId
FROM
[Data\Goal line for QV.xlsx]
(ooxml, embedded labels, table is Sheet1);
What I need to do at this point is left join on the LinkTable to insert IdClient (as Key|Client) whenever:
Key|Resellers from LinkTable matches CL.IdReseller OR
Key|ReferralSystemId from LinkTable matches CL.ReferralSystemId
My most recent attempt was to add if(isnull()) logic in the left join to pass null as Key|ReferralSystems whenever Key|Resellers is valid (and vice versa for Key|Resellers):
Left Join (LinkTable)
LOAD
If(IsNull(Key|Resellers)=0,
NULL(),
Key|Resellers)
as Key|Resellers,
If(IsNull(Key|Resellers)=0,
Key|ReferralSystems,
NULL())
as Key|ReferralSystems,
If(IsNull(Key|Resellers)=0,
ApplyMap('MapRefSys',Key|ReferralSystems,'NULL'),
ApplyMap('MapReseller',Key|Resellers,'NULL'))
as Key|Client
Resident LinkTable;
Since QV joins on all fields with matching field names, this logic will not work.
Please help guide me out of the snake pit and towards the sunlight!
