Apply Map

[bharatkishore]

Hi All,

I have 2 tables one table is like

T1:

LOAD Modality,

     Material_Code as  [System Code],

     PartType as  [Norm group],

     PLANT

FROM

(qvd);

and second table is like

T2:

LOAD Modality,

     [Product Group],

     [System Code],

     [Norm group],

     Global_Norm,

     CN_Norm,

     RUID_Norm

FROM

(qvd);

Now in first qvd i have plant and in that plant i have CN93, ID93 ,Global, RU93 data.

Now in second qvd i have CN_Norm, RUID_Norm and Global.

Now based on first field plant when it is CN93 i should get sum(CN_Norm) and when it is RU93 i should get sum(RUID_Norm ) and when it is Globali should get sum(Global) .

Can you please help me how can i acheive it.

Do i need to use apply map or any other.

Attached app for more reference.

Thanks,

Bharat

[sunny_talwar]

Now based on first field plant when it is CN93 i should get sum(CN_Norm) and when it is RU93 i should get sum(RUID_Norm ) and when it is Globali should get sum(Global).

Get the sums where? In the script, front end? Would you be able to demonstrate this with a help of an example from your dataset?

[bharatkishore]

HI Sunny,

In the script only i need to derive.

Now in first table i have

T1:

LOAD Modality,

    Material_Code as  [System Code],

    PartType as  [Norm group],

    PLANT

FROM

(qvd);

and in second table i need to get

T2:

LOAD Modality,

    [Product Group],

    [System Code],

    [Norm group],

    sum(Global_Norm) as Global_Norm ,(Based on if plant is Global)

    sum( CN_Norm) as CN_Norm,(Based on if plant is CN93)

    sum(RUID_Norm) as RUID_Norm (Based on if plant is RU93)

FROM

(qvd);

And in the above table i need to use  Material_Code as  [System Code],    PartType as  [Norm group] as well.

to be matched second table as well.

PFA app where i have shown in chart.

Please let me know if you need anything more.

[sunny_talwar]

May be this

T1:

Mapping

LOAD Modality & '|' & Material_Code & '|' & PartType,

    PLANT

FROM (qvd);

T2:

LOAD Modality,

    [Product Group],

    [System Code],

    [Norm group],

    Sum(If(ApplyMap('T1', Modality & '|' & [System Code] & '|' & [Norm group]) = 'Global', Global_Norm)) as Global_Norm,

    Sum(If(ApplyMap('T1', Modality & '|' & [System Code] & '|' & [Norm group]) = 'CN93', CN_Norm) as CN_Norm,

    Sum(If(ApplyMap('T1', Modality & '|' & [System Code] & '|' & [Norm group]) = 'RU93', RUID_Norm) as RUID_Norm

FROM (qvd);

[bharatkishore]

Hi Sunny,

Sorry getting error as Invalid expression.

I am attaching the qvd's as well.

Hope it will be helpful.

[sunny_talwar]

My bad, before I try to run this.. can you try to add Group By Statement

T1:

Mapping

LOAD Modality & '|' & Material_Code & '|' & PartType,

    PLANT

FROM (qvd);

T2:

LOAD Modality,

    [Product Group],

    [System Code],

    [Norm group],

    Sum(If(ApplyMap('T1', Modality & '|' & [System Code] & '|' & [Norm group]) = 'Global', Global_Norm)) as Global_Norm,

    Sum(If(ApplyMap('T1', Modality & '|' & [System Code] & '|' & [Norm group]) = 'CN93', CN_Norm) as CN_Norm,

    Sum(If(ApplyMap('T1', Modality & '|' & [System Code] & '|' & [Norm group]) = 'RU93', RUID_Norm) as RUID_Norm

FROM (qvd)

Group By Modality, [Product Group], [System Code], [Norm group];

[sasiparupudi1]

May be like this?

T2:

LOAD Modality,

     [Product Group],

     [System Code],

     [Norm group],

     Global_Norm,

     CN_Norm,

     RUID_Norm

FROM

(qvd);

T3:

LOAD

'CN93' As [System Code],

Sum(CN_Norm) As Amount

Resident  T2;

Concatenate(T3)

LOAD

'RU93 ' As [System Code],

Sum(RUID_Norm) As Amount

Resident  T2;

Concatenate(T3)

LOAD

'Global' As [System Code],

Sum(Global_Norm) As Amount

Resident  T2;

Map_Amount:

Mapping Load

[System Code],

Amount

Resident T3;

Drop Table T3;

T1:

LOAD

Modality,

Material_Code as  [System Code],

ApplyMap('Map_Amount',Material_Code,0) As Amount,

PartType as  [Norm group],

PLANT

FROM

(qvd);

[bharatkishore]

Sorry Sunny.

I tried but got got all 0.

And i need to see plant field as well. As of now i cannot see plant.

And no need to take Modality & '|' &

Just i need Material_Code & '|' & PartType, and Material_code should be renamed as [System Code] and PartType should be renamed as [Norm Group] and then Plant as well.

[bharatkishore]

Hi Sasidhar,

I tried but i am not anything for plant just i am getting -.for Plant when i take in chart..

[sunny_talwar]

Would you be able to provide an example of where you wish to see a value and what value from your T2 qvd file?