How to load Fact Data base on Master Data only?

[jim_chan]

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

[its_anandrjs]

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

[its_anandrjs]

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

Fact:

Load * Inline

[

Transport Number

T1

T2

T3

T4

T5

T6

T7

T7

];

Right Keep(Fact)

Master:

LOAD * Inline

[

Transport Number

T1

T2

T3

];

[its_anandrjs]

No Sample Attached.

[jim_chan]

y cant i use inner join???

Master:

LOAD * Inline

[

Transport Number

T1

T2

T3

];

inner join

Fact:

Load * Inline

[

Transport Number

T1

T2

T3

T4

T5

T6

T7

T7

];

[jim_chan]

Sorry. My Bad.

[its_anandrjs · Accepted solution]

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

];

[its_anandrjs]

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);

[jim_chan]

so right keep or where exists?