Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Join 2 tables on load with a where

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

TrayReport
ApplicationTrayStatusApplicationAmount
12311111StartRDY12311111100
12311111StartCMP32122222200
12311111ReviewCMP222222220
12311111ShopRDY
12311111ShopCMP
12311111CJARDY
12311111CJACMP
12311111GoodCMP
12311111Post FinishCMP
32122222StartCMP
32122222ReviewCMP
32122222ShopRDY
32122222ShopCMP
32122222GoodCMP
32122222Post FinishCMP
22222222StartRDY
22222222StartCMP
22222222ReviewCMP
22222222ShopRDY
22222222ShopCMP
22222222CJARDY
22222222CJACMP
22222222GoodCMP
22222222Post FinishCMP
8 Replies
rustyfishbones
Master II
Master II

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
Master III
Master III

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

See the sample

Not applicable
Author

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

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

Not applicable
Author

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

Not applicable
Author

Hi,

Check the File Attached .

Hope it helps You to achieve what you want.

Revert Back Fine any queries.

Regards,

Ravikant

Not applicable
Author

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

TrayApplicationStatusFlowResult
CJA12311111CMPDeptGroup
CJA12311111RDYOpenGroup
Good12311111CMPDeptGroup
Post Finish12311111CMPDeptGroup
Review12311111CMPDeptGroup
Shop12311111CMPDeptGroup
Shop12311111RDYOpenGroup
Start12311111CMPDeptGroup
Start12311111RDYOpenGroup
CJA22222222CMPOpenDept
CJA22222222RDYOpenDept
Good22222222CMPOpenDept
Post Finish22222222CMPOpenDept
Review22222222CMPOpenDept
Shop22222222CMPOpenDept
Shop22222222RDYOpenDept
Start22222222CMPOpenDept
Start22222222RDYOpenDept
Good32122222CMPDeptOpen
Post Finish32122222CMPDeptOpen
Review32122222CMPDeptOpen
Shop32122222CMPDeptOpen
Shop32122222RDYOpenOpen
Start32122222CMPDeptOpen
Not applicable
Author

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

TrayApplicationStatusAmountResultConcatenate
CJA12311111CMP200GroupCJACMP
CJA12311111RDY200GroupCJARDY
Good12311111CMP200GroupGoodCMP
Post Finish12311111CMP200GroupPost FinishCMP
Review12311111CMP200GroupReviewCMP
Shop12311111CMP200GroupShopCMP
Shop12311111RDY200GroupShopRDY
Start12311111CMP200GroupStartCMP
Start12311111RDY200GroupStartRDY
CJA22222222CMP0DeptCJACMP
CJA22222222RDY0DeptCJARDY
Good22222222CMP0DeptGoodCMP
Post Finish22222222CMP0DeptPost FinishCMP
Review22222222CMP0DeptReviewCMP
Shop22222222CMP0DeptShopCMP
Shop22222222RDY0DeptShopRDY
Start22222222CMP0DeptStartCMP
Start22222222RDY0DeptStartRDY
Good32122222CMP100OpenGoodCMP
Post Finish32122222CMP100OpenPost FinishCMP
Review32122222CMP100OpenReviewCMP
Shop32122222CMP100OpenShopCMP
Shop32122222RDY100OpenShopRDY
Start32122222CMP100OpenStartCMP

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

Not applicable
Author

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

1dd

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

1ddxx