Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

joakimwasseng
Not applicable

Need help to join three tables

orderNocustomerdeliveryDate
11012011-02-10
21012012-03-11
31022012-03-10
41022013-04-15
51032013-04-16

customercustomerGroup
1011
1021
1032

customerGroupYearBonus
120111,5%
120122,0%
120132,0%
220110,5%
220120,75%
220131,2%


Ok, so I have three tables that I need to JOIN and need help to figure out how to do it in QV.

Its easy enough to LEFT JOIN table2 onto table1 since they have field "customer" in common.

But then I need to LEFT JOIN table3 onto table1 using field "customerGroup" that only exists in table2 AND check that the table3.Year" = Year(table1.deliveryDate)

All fields are QUALIFIED and I create my own UNQUALIFIEDkeys between the the three tables...

Hope you understand

Tags (2)
1 Reply
Not applicable

Re: Need help to join three tables

Here is what I did based on your examples given, making inline tables for the data above:

Table1:

LOAD * Inline [

orderNo, customer, deliveryDate

1, 101, 2011-02-10

2, 101, 2012-03-11

3, 102, 2012-03-10

4, 102, 2013-04-15

5, 103, 2013-04-16

];

left join

LOAD * Inline [

customer, customerGroup

101, 1

102, 1

103, 2

];

Table2:

LOAD *,

YEAR(deliveryDate) as deliveryYear

Resident Table1;

drop table Table1;

left join

LOAD * Inline [

customerGroup, Year, Bonus

1, 2011, .015

1, 2012, .02

1, 2013, .02

2, 2011, .005

2, 2012, .0075

2, 2013, .012

]

;

Table3:

LOAD *,

' ' as Junk

Resident Table2

WHERE deliveryYear = Year;

drop table Table2;

The resulting data set you are looking for it in Table3.

I also attached the .qvw for additional reference.

Hope this helps!