topic Re: Join Two Table Data in QlikView

Join Two Table Data

girish_talele — Thu, 21 Aug 2014 09:29:56 GMT

Dear Experts,

I want to join two tables, first contains Bill Of Material & second contains Order Issue Data.

I want to compare Required v/s Actual Issue data.

The Data in BOM & Order Issue is as given below:-

Resulting table required as given below:-

Please help in the same.

Re: Join Two Table Data

Thu, 21 Aug 2014 09:48:00 GMT

The result is comes by joining the two tables

By using Outer join we can get the above Result

Re: Join Two Table Data

mjayachandran — Thu, 21 Aug 2014 10:09:49 GMT

Check attached :

Just use Outer join

Re: Join Two Table Data

girish_talele — Thu, 21 Aug 2014 10:19:46 GMT

Dear Sasikumar & Mahesh,

By Outer Join or making common field, the data is not getting arranged in desired manner.

For each row of first table, second tables all rows are getting repeated. And hence the 60 rows are populated.

Any other solution ???

Re: Join Two Table Data

jagan — Thu, 21 Aug 2014 10:24:45 GMT

Hi,

Try this script

BOM:

Load

P_MatId,

P_PrdNo,

P_MatId & P_GICompId AS Key,

P_GICompId,

P_CompGIQty

Inline [

P_PrdNo, P_MatId, P_GICompId, P_CompGIQty

Ord1, Mat1, Comp1, 10

Ord1, Mat1, Comp2.1, 20

Ord1, Mat1, Comp3, 30

Ord1, Mat1, Comp4, 40

Ord1, Mat1, Comp4.1, 40

Ord2, Mat2, Comp5, 50

Ord2, Mat2, Comp6.1, 60

Ord2, Mat2, Comp7, 70

Ord2, Mat2, Comp8, 80

Ord2, Mat2, Comp8.1, 80

Ord3, Mat1, Comp1, 10

Ord3, Mat1, Comp2.1, 20

Ord3, Mat1, Comp3, 30

Ord3, Mat1, Comp4, 40

Ord3, Mat1, Comp5.1, 40

];

LEFT JOIN

Load * ,

B_MatId & B_CompId AS Key

Inline [

B_MatId, B_CompId, B_CompReqQty

Mat1, Comp1, 10

Mat1, Comp2, 20

Mat1, Comp3, 30

Mat1, Comp4, 40

Mat2, Comp5, 50

Mat2, Comp6, 60

Mat2, Comp7, 70

Mat2, Comp8, 80

];

Regards,

Jagan.

Re: Join Two Table Data

girish_talele — Thu, 21 Aug 2014 10:37:16 GMT

Dear Jagan,

The Join is not Woking, e.g in Left Join the "Comp2" is missing.

Re: Join Two Table Data

girish_talele — Thu, 21 Aug 2014 12:15:13 GMT

Dear Experts,

Any other solution???

Re: Join Two Table Data

saradhi_it — Thu, 21 Aug 2014 12:23:38 GMT

Did you used force concatnation

Re: Join Two Table Data

girish_talele — Thu, 21 Aug 2014 12:25:29 GMT

No,

How to use force concatenation.

Please suggest.

Re: Join Two Table Data

saradhi_it — Thu, 21 Aug 2014 12:40:34 GMT

concatenation won't work for u r requirement use straight table that is best...bcos you can shift rows as you want,,,,,,

Re: Join Two Table Data

Thu, 21 Aug 2014 18:02:22 GMT

Girish,

This is less of a QlikView problem and more of a data structure and logic problem.

You have CompID's with suffixes, i.e. "Comp2.1", in the Order table that have no corresponding entries in the BOM table. That alone is not a problem.

The problem is that you seem to be applying logic differently for the records with a suffix. In your Result table, rows #2 and 12 assign a Requested Quantity value, even though there is no match to the BOM table. On rows #5, 10 and 15, you do not assign Requested Quantity values.

It appears you want to test for the existence of a row without a suffix value and take different actions if you find one or don't find one.

Record level logic, i.e. joins, won't solve this problem as it appears you need to process groups of records together to determine the right course of action.

I am new to QlikView, so I do not know if this is possible in the tool. I would probably write some code to prepare the data before loading into QlikView.

Re: Join Two Table Data

vardhancse — Thu, 21 Aug 2014 18:05:52 GMT

Try concatenate both the tables

Re: Join Two Table Data

girish_talele — Sat, 23 Aug 2014 05:51:56 GMT

Dear All,

Thanks for helping, As Mark said, the requirement was quite odd.

I Manage to derive required result (although with too many table).

Thanks for your support.

Regards,

Girish.