topic Re: Particular Join between tables in QlikView

Particular Join between tables

Fri, 13 Mar 2015 13:17:00 GMT

Hello,

I've got a this one thing that I can't seem to tackle.

I've got two tables. One is a fact table, and the other is a dimension table.

I need to be able to flag quantities that do not meet the min order condition. The min values set for each material are found on the dimension table.

When I join then, I can't seem to get the minimal order quantity column next to each line of the fact table.

Here's the fact table:

Date Mat OrderQty

12/01 A 2

13/01 A 1

14/01 A 0,5

12/01 B 1

13/01 B 2

14/01 B 0,8

12/01 C 0,9

13/01 C 2

14/01 C 3

Here's the dimension table:

Mat Min Order

A 0,6

B 0,8

C 0,9

Does anybody have any ideas on how to achieve this?

Thank you very much.

Re: Particular Join between tables

PrashantSangle — Fri, 13 Mar 2015 13:20:19 GMT

Hi,

USe Outer Join

Load *

from Fact_Table;

join

Load *

from Dimension_Table;

Regards

Re: Particular Join between tables

PrashantSangle — Fri, 13 Mar 2015 13:25:26 GMT

Hi,

You script became,

Test

LOAD * INLINE [
Date, Mat,OrderQty
12/01,A,2
13/01,A,1
14/01,A,0.5
12/01,B,1
13/01,B,2
14/01,B,0.8
12/01,C,0.9
13/01,C,2
14/01,C,3
];

join
LOAD * INLINE [
Mat,Min Order
A,0.6
B,0.8
C,0.9
];

then take table Box

add All 4 Field

Regards,

Re: Particular Join between tables

Fri, 13 Mar 2015 13:31:47 GMT

Hi,

Try like this.

FactTable:

Load

Date,

Mat ,

OrderQty

from path ;

concatenate(FactTable)

Load

Mat,

Min Order as OrderQty

from path;

FactTable:

Load

Date,

Mat ,

OrderQty

from path ;

left join(FactTable)

Load

Mat,

Min Order

from path;

Re: Particular Join between tables

sorrakis01 — Fri, 13 Mar 2015 13:33:05 GMT

Hi,

The same of my collegues, but do you like this?

Regards

Re: Particular Join between tables

buzzy996 — Fri, 13 Mar 2015 13:40:18 GMT

hopes ur looking for,

after loading ur data in qv,on table n expression tab u have to create one field,

for Flag and expression is

=If(Mat='A' and [Min Order]>='0.6','Met',

If(Mat='B' and [Min Order]>='0.8','Met',

If(Mat='C' and [Min Order]>='0.9','Met','Not Met')))