Join 2 tables on load with a where

[#1]

hi Guys

I am loading from 2 excel sheet into qlikview see below :

What I am looking to do at load is

When an Application has been in TRAY CJA at Status = CMP AND Amount is Greater than 1 then Flow = "Group",

When an Application has NOT BEEN in TRAY CJA at Status = CMP AND Amount is Greater than 1 then  Flow = "Dept",

Otherwise Flow = "Open"

I have tried concatenating the 2 sheets at load but I cant quite get it - any advice would really be appreciated

Thanks

A

Tray			Report
Application	Tray	Status	Application	Amount
12311111	Start	RDY	12311111	100
12311111	Start	CMP	32122222	200
12311111	Review	CMP	22222222	0
12311111	Shop	RDY
12311111	Shop	CMP
12311111	CJA	RDY
12311111	CJA	CMP
12311111	Good	CMP
12311111	Post Finish	CMP
32122222	Start	CMP
32122222	Review	CMP
32122222	Shop	RDY
32122222	Shop	CMP
32122222	Good	CMP
32122222	Post Finish	CMP
22222222	Start	RDY
22222222	Start	CMP
22222222	Review	CMP
22222222	Shop	RDY
22222222	Shop	CMP
22222222	CJA	RDY
22222222	CJA	CMP
22222222	Good	CMP
22222222	Post Finish	CMP

[rustyfishbones]

Anne,

Qlikview will automatically join the tables if they contain 1 unique field that's the same name

So in other words If you have a table called Tray and a table called Report they will join together as both contain

Application.

If they contain more than 1 common field name, a synthetic table will be created

[sujeetsingh]

I have derived it as Flow a new field in script.

See the sample

[#4]

hi

see this example , hope this helps you

Outer

The explicit Join prefix can be preceded by the prefix outer in order to specify an outer join. In an outer join all combinations between the two tables are generated. The outer keyword is optional.

outer join [ (tablename ) ](loadstatement |selectstatement )

Example:

Table1
A	B
1	aa
2	cc
3	ee

Table2
A	C
1	xx
4	yy

load * from table1;

join

load * from table2;

***********************

OR

load * from table1;

outer join

  load * from table2;

Joined table
A	B	C
1	aa	xx
2	cc	-
3	ee	-
4	-	yy

*****************************************************************************************

[#5]

Hi Alan

They are joining based on the field Application, but I wish to apply a logic that falls over the 2 spreadsheets, so when in sheet [Tray] if the application has been in CJA at staus CMP AND in Sheet [Report] if Amount is greater tha 0 create a field called flow and value would be group, so with the data above , 12311111 should be Group,32122222 sould be Dept and 22222222 should be Open

This is the bit I'm having trouble with

Thanks

A

[#6]

Hi,

Check the File Attached .

Hope it helps You to achieve what you want.

Revert Back Fine any queries.

Regards,

Ravikant

[#7]

Hi Ravikant

Thanks for respones, one of the criteria was amount and also when its in the CMP & CJA I would want the Flow for all instances to change to the relevant answer.  See below - thanks a mill for looking at this , you have helped me figure out the join part

Thanks

A

Tray	Application	Status	Flow	Result
CJA	12311111	CMP	Dept	Group
CJA	12311111	RDY	Open	Group
Good	12311111	CMP	Dept	Group
Post Finish	12311111	CMP	Dept	Group
Review	12311111	CMP	Dept	Group
Shop	12311111	CMP	Dept	Group
Shop	12311111	RDY	Open	Group
Start	12311111	CMP	Dept	Group
Start	12311111	RDY	Open	Group
CJA	22222222	CMP	Open	Dept
CJA	22222222	RDY	Open	Dept
Good	22222222	CMP	Open	Dept
Post Finish	22222222	CMP	Open	Dept
Review	22222222	CMP	Open	Dept
Shop	22222222	CMP	Open	Dept
Shop	22222222	RDY	Open	Dept
Start	22222222	CMP	Open	Dept
Start	22222222	RDY	Open	Dept
Good	32122222	CMP	Dept	Open
Post Finish	32122222	CMP	Dept	Open
Review	32122222	CMP	Dept	Open
Shop	32122222	CMP	Dept	Open
Shop	32122222	RDY	Open	Open
Start	32122222	CMP	Dept	Open

[#8]

Hi Guys

If I was to do this in Excel, I would create a column of

=Concatenate(Tray,Status)  - say in colum G

A                    B                         C               E               F               G

Tray	Application	Status	Amount	Result	Concatenate
CJA	12311111	CMP	200	Group	CJACMP
CJA	12311111	RDY	200	Group	CJARDY
Good	12311111	CMP	200	Group	GoodCMP
Post Finish	12311111	CMP	200	Group	Post FinishCMP
Review	12311111	CMP	200	Group	ReviewCMP
Shop	12311111	CMP	200	Group	ShopCMP
Shop	12311111	RDY	200	Group	ShopRDY
Start	12311111	CMP	200	Group	StartCMP
Start	12311111	RDY	200	Group	StartRDY
CJA	22222222	CMP	0	Dept	CJACMP
CJA	22222222	RDY	0	Dept	CJARDY
Good	22222222	CMP	0	Dept	GoodCMP
Post Finish	22222222	CMP	0	Dept	Post FinishCMP
Review	22222222	CMP	0	Dept	ReviewCMP
Shop	22222222	CMP	0	Dept	ShopCMP
Shop	22222222	RDY	0	Dept	ShopRDY
Start	22222222	CMP	0	Dept	StartCMP
Start	22222222	RDY	0	Dept	StartRDY
Good	32122222	CMP	100	Open	GoodCMP
Post Finish	32122222	CMP	100	Open	Post FinishCMP
Review	32122222	CMP	100	Open	ReviewCMP
Shop	32122222	CMP	100	Open	ShopCMP
Shop	32122222	RDY	100	Open	ShopRDY
Start	32122222	CMP	100	Open	StartCMP

and then

=IF(AND(E2>0,SUMPRODUCT(--($B$2:$B$25=B2),--($G$2:$G$25="CJACMP"))>0),"Group",IF(AND(E2>0,SUMPRODUCT(--($B$2:$B$25=B2),--($G$2:$G$25="CJACMP"))=0),"Open","Dept"))

This would give me my results column

Thanks

A

[#9]

Hi Vishwaranjan

Thanks for the above - but my sample data is slightly different - ie there may be more than 1 instance of application - I have amended below to reflect the data,

I think tehe main issue for me is once the tables are joined how to use the relationship to categorise correctly, I will post another thread

Thanks

A

Example:

Table1
A	B
1	aa
2	cc
3	ee
1	dd

Table2
A	C
1	xx
4	yy

load * from table1;

join

load * from table2;

***********************

OR

load * from table1;

outer join

  load * from table2;

Joined table
A	B	C
1	aa	xx
2	cc	-
3	ee	-
4	-	yy
1	dd	xx