composite key

[kunkumnaveen]

Hello All,

let say i got two table with data as below,

Table1:

            Supplier    Material    plant                          

                s1          A              A001

              s2          B              A002

              s1          C           A003

Table2:

                z_part    component      plant

                z1          A                A001

              z2          B                A002

so linking these tables by creating a composite key

which is      material&plant as Key from table1

                  component&plant as Key from table2

finally what i get is this

              Key

              AA001

              BA002

              CA003

what i am looking is i need a new column ,which should compare with z_part field ,i mean if there is a value in z_part field against that particular Key then new column value should be that z_part value else it should be Material value from Table1

Final Output  I need is

Key          New_Cloumn

AA001          z1

BA002          z2

CA003            C

from above table CA003  key values has not z_part value so in new column field  the value is  C from material field

how can i do this at script level

Thanks

[kunkumnaveen]

Any Suggestion in achieving this requirement please....

[antoniotiman]

Hi Naveen,

see Attachment.

Regards,

Antonio

[Kushal_Chawda]

Table2:

mapping LOAD component&plant as Key,

             z_part

Inline [

                z_part,    component,      plant

                z1,          A ,               A001

              z2,          B ,               A002 ];

Table1:

LOAD *,

     if(Z_Part1='NA',Material,Z_Part1) as Z_Part;

LOAD *,ApplyMap('Table2',Material&plant,'NA') as Z_Part1 

Inline [

            Supplier,    Material,    plant                         

                s1,          A ,             A001

              s2,          B,              A002

              s1,          C ,          A003 ];

[kunkumnaveen]

HI,thanks for reply could u plz copy paste the script please ,as i am not allow to  copy any qvw files in server...plz

thanks

[antoniotiman]

Table1:
LOAD * Inline [
Supplier Material plant 
s1 A A001
s2 B A002
s1 C A003
](delimiter is spaces);
Table2:
Join
LOAD * Inline [
z_part component plant
z1 A A001
z2 B A002
](delimiter is spaces);

LOAD Material&plant as Key,If(IsNull(z_part),Material,z_part) as New_Column
Resident Table1;
Drop Table Table1;

[Kushal_Chawda]

Map:

mapping LOAD component&plant as Key,

             z_part

FROM table 2;

Data:

LOAD *,

     if(Z_Part1='NA',Material,Z_Part1) as Z_Part;

LOAD Supplier,

          ApplyMap('Map',Material&plant,'NA') as Z_Part1

           Material,

           Plant

FROM table1;