topic Re: How to load Fact Data base on Master Data only? in QlikView

How to load Fact Data base on Master Data only?

jim_chan — Mon, 02 Oct 2017 07:31:45 GMT

hi guys,

I have Master Data and 1 Fact data.

Master table has a field called Transport Number with data T1,T2,T3. But my Fact Data has a Transport Number field that has data T1,T2,T3,T4,T5,T6,T7.

I want my fact data only show data out as per the Master Table. If the Master Table only have Transport Number of T1,T2,T3. then only show T1,T2,T3 data in the Fact Table.

I need to do it in the loading script. I have also attach this sample.

Rgds

Jim

Re: How to load Fact Data base on Master Data only?

its_anandrjs — Mon, 02 Oct 2017 07:34:16 GMT

Use Left Keep or Left Join master to the fact table.

Re: How to load Fact Data base on Master Data only?

its_anandrjs — Mon, 02 Oct 2017 07:37:04 GMT

My bad use Right Keep on the Fact table with get only data based on the Master Table.

Fact:

Load * Inline

[

Transport Number

];

Right Keep(Fact)

Master:

LOAD * Inline

[

Transport Number

];

Re: How to load Fact Data base on Master Data only?

its_anandrjs — Mon, 02 Oct 2017 07:38:47 GMT

No Sample Attached.

Re: How to load Fact Data base on Master Data only?

jim_chan — Mon, 02 Oct 2017 07:51:40 GMT

y cant i use inner join???

Master:

LOAD * Inline

[

Transport Number

];

inner join

Fact:

Load * Inline

[

Transport Number

];

Re: How to load Fact Data base on Master Data only?

jim_chan — Mon, 02 Oct 2017 07:57:14 GMT

Sorry. My Bad.

Re: How to load Fact Data base on Master Data only?

its_anandrjs — Mon, 02 Oct 2017 08:03:39 GMT

Every time Fact behaves as a transactional data in itself and if you use inner join that effects the result somehow.

You can use Right Join, Right Join, Where exits also

Fact:

LOAD * INLINE [

F1, F2, transport_number

qwe, q, T11

eqwe, a, T23

cbfgn, z, T1

werf, xs, T2

tryry, w, T3

qwewqe, f, T40

hmghmh, e, T50

wqewqe, v, T60

];

Master:

Left Keep(Fact)

LOAD * INLINE [

location_name, transport_number

a, T1

b, T2

c, T3

];

Re: How to load Fact Data base on Master Data only?

its_anandrjs — Mon, 02 Oct 2017 08:06:03 GMT

With where Exists see the attached also.

Master:

LOAD * INLINE [

location_name, transport_number

a, T1

b, T2

c, T3

];

Fact:

LOAD * INLINE [

F1, F2, transport_number

qwe, q, T11

eqwe, a, T23

cbfgn, z, T1

werf, xs, T2

tryry, w, T3

qwewqe, f, T40

hmghmh, e, T50

wqewqe, v, T60

] Where Exists(transport_number);

Re: How to load Fact Data base on Master Data only?

jim_chan — Mon, 02 Oct 2017 08:24:11 GMT

so right keep or where exists?