New field creation from two tables

[mahitham]

Hi Experts,

Can any one please help me on below two requirements.

I have two tables like below,

1. From these two tables need to create Rating field based on below condition

If [Code Level]= A or B then need to extract HighRating else LowRating

Here HighRating &LowRating or coming from Table1 and [Code Level] is coming from Table 2. These is no common field between these two tables to join.

2. For all [Code Level] types need to extract Group

Please help me on this.

Table2:

LOAD * INLINE [

    Network, Code A, Code B, Code C,High Rating,Low Rating,Group

    A, FRIGJ, HUKO, LKIOG,10,8, Service

    B, , 86938, IKRLP,2,6,Sales

    C, , OKLIP, OKLI,10,6,Service

];

Table1:

Load  *,

If(Exists([Code A], NAME), 'A',

If(Exists([Code B], NAME), 'B',

If(Exists([Code C], NAME), 'C', 'D'))) as [Code Level];

LOAD * INLINE [

    Key, NAME, Amount

    16/07/2018| FRIGJ-A1, FRIGJ, 0.56893

    16/07/2018| FRIGJ-A2, FRIGJ, 0.16893

    17/07/2018| FRIGJ-A1, FRIGJ, 8.26893

    17/07/2018| FRIGJ-A2, FRIGJ, 9.26893

    16/07/2018|IKRLP-A1, IKRLP, 2.59688

    16/07/2018|IKRLP-A2, IKRLP, 2.86592

    17/07/2018|IKRLP-A1, IKRLP, 2.59688

    17/07/2018|IKRLP-A2, IKRLP, 28.5968

    16/07/2018|OKLP-A1, OKLP, 2.59688

    16/07/2018|OKLP-A2, OKLP, 2.86592

    17/07/2018|OKLP-A1, OKLP, 2.59688

    17/07/2018|OKLP-A2, OKLP, 28.5968

];

Thanks in advance.

[YoussefBelloum]

Hi,

what is extraction rule/logic if the table are not connected ?

if on one row, you have STATUS=A OR B, you want to extract the HighRating value of which row ?

[sunny_talwar]

What is the raw data look like? You provided the expected output, but didn't provide the raw data which would lead to this output...

[mahitham]

[sunny_talwar · Accepted solution]

Try this

Table2:

LOAD * INLINE [

    Network, Code A, Code B, Code C, High Rating, Low Rating, Group

    Global, FRIGJ, HUKO, LKIOG, 2.8, 2.8, Service

    Local, , 86938, IKRLP, 0, 6, Sales

    Universal, , OKLIP, OKLI, 4.5, 6, Service

];

Table:

LOAD *,

If(Exists([Code A], NAME), 'A',

If(Exists([Code B], NAME), 'B',

If(Exists([Code C], NAME), 'C', 'D'))) as [Code Level];

LOAD * INLINE [

    Key, NAME, Amount

    16/07/2018| FRIGJ-A1, FRIGJ, 0.56893

    16/07/2018| FRIGJ-A2, FRIGJ, 0.16893

    17/07/2018| FRIGJ-A1, FRIGJ, 8.26893

    17/07/2018| FRIGJ-A2, FRIGJ, 9.26893

    16/07/2018|IKRLP-A1, IKRLP, 2.59688

    16/07/2018|IKRLP-A2, IKRLP, 2.86592

    17/07/2018|IKRLP-A1, IKRLP, 2.59688

    17/07/2018|IKRLP-A2, IKRLP, 28.5968

    16/07/2018|OKLP-A1, OKLIP, 2.59688

    16/07/2018|OKLP-A2, OKLIP, 2.86592

    17/07/2018|OKLP-A1, OKLIP, 2.59688

    17/07/2018|OKLP-A2, OKLIP, 28.5968

];

Left Join (Table)

LOAD [Code A] as NAME,

Group as GroupA,

[High Rating] as RatingA

Resident Table2;

Left Join (Table)

LOAD [Code B] as NAME,

Group as GroupB,

[High Rating] as RatingB

Resident Table2;

Left Join (Table)

LOAD [Code C] as NAME,

Group as GroupC,

[Low Rating] as RatingC

Resident Table2;

FinalTable:

LOAD *,

If(Len(Trim(RatingA)) > 0, RatingA,

If(Len(Trim(RatingB)) > 0, RatingB, RatingC)) as Rating,

If(Len(Trim(GroupA)) > 0, GroupA,

If(Len(Trim(GroupB)) > 0, GroupB, GroupC)) as Group

Resident Table;

DROP Tables Table, Table2;

[sunny_talwar]

Having said this, I feel there should be an easier way to do this.. I am working on it and will get back when I find one

[mahitham]

Thank you sunny for your continuous help

[sunny_talwar]

Try this as this might be slightly better

Table2:

LOAD * INLINE [

    Network, Code A, Code B, Code C, High Rating, Low Rating, Group

    Global, FRIGJ, HUKO, LKIOG, 2.8, 2.8, Service

    Local, , 86938, IKRLP, 0, 6, Sales

    Universal, , OKLIP, OKLI, 4.5, 6, Service

];

Table:

LOAD Network,

[Code A] as NAME,

[High Rating] as Rating,

Group,

'A' as [Code Level]

Resident Table2

Where Len(Trim([Code A])) > 0;

Concatenate (Table)

LOAD Network,

[Code B] as NAME,

[High Rating] as Rating,

Group,

'B' as [Code Level]

Resident Table2

Where Len(Trim([Code B])) > 0;

Concatenate (Table)

LOAD Network,

[Code C] as NAME,

[Low Rating] as Rating,

Group,

'C' as [Code Level]

Resident Table2

Where Len(Trim([Code C])) > 0;

DROP Table Table2;

FinalTable:

LOAD * INLINE [

    Key, NAME, Amount

    16/07/2018| FRIGJ-A1, FRIGJ, 0.56893

    16/07/2018| FRIGJ-A2, FRIGJ, 0.16893

    17/07/2018| FRIGJ-A1, FRIGJ, 8.26893

    17/07/2018| FRIGJ-A2, FRIGJ, 9.26893

    16/07/2018|IKRLP-A1, IKRLP, 2.59688

    16/07/2018|IKRLP-A2, IKRLP, 2.86592

    17/07/2018|IKRLP-A1, IKRLP, 2.59688

    17/07/2018|IKRLP-A2, IKRLP, 28.5968

    16/07/2018|OKLP-A1, OKLIP, 2.59688

    16/07/2018|OKLP-A2, OKLIP, 2.86592

    17/07/2018|OKLP-A1, OKLIP, 2.59688

    17/07/2018|OKLP-A2, OKLIP, 28.5968

];

Left Join (FinalTable)

LOAD NAME,

Rating,

Group,

[Code Level]

Resident Table;

DROP Table Table;