topic Re: Apply Map in QlikView

Apply Map

bharatkishore — Mon, 05 Mar 2018 13:03:00 GMT

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

Re: Apply Map

sunny_talwar — Mon, 05 Mar 2018 13:17:40 GMT

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?

Re: Apply Map

bharatkishore — Mon, 05 Mar 2018 13:32:10 GMT

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.

Re: Apply Map

sunny_talwar — Mon, 05 Mar 2018 13:39:52 GMT

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

Re: Apply Map

bharatkishore — Mon, 05 Mar 2018 13:44:20 GMT

Hi Sunny,

Sorry getting error as Invalid expression.

I am attaching the qvd's as well.

Hope it will be helpful.

Re: Apply Map

sunny_talwar — Mon, 05 Mar 2018 13:47:47 GMT

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

Re: Apply Map

sasiparupudi1 — Mon, 05 Mar 2018 13:54:12 GMT

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

Re: Apply Map

bharatkishore — Mon, 05 Mar 2018 13:55:27 GMT

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.

Re: Apply Map

bharatkishore — Mon, 05 Mar 2018 14:06:07 GMT

Hi Sasidhar,

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

Re: Apply Map

sunny_talwar — Mon, 05 Mar 2018 14:06:18 GMT

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

Re: Apply Map

sasiparupudi1 — Mon, 05 Mar 2018 14:14:54 GMT

T1:

LOAD

Modality,

Material_Code as [System Code],

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

PartType as [Norm group],

PLANT

FROM

(qvd);

Re: Apply Map

bharatkishore — Mon, 05 Mar 2018 14:29:35 GMT

I will try to explain bit clearly.

In Table1 i have

Modality,

Material_Code a

PartType

PLANT

and in plant i have CN93, RU93 and Global

and in Table 2 in have CN_Norm,Global and RU_Norm.

Now in final table i need to get

Modality ,Material_Code , PartType, CN_Norm,Global and RU_Norm

Now from first qvd if plant is CN93 it should take CN_Norm from second table and if plant is RU93 it should take RU_Norm from second table, and last if plant is not CN_Norm and RU_Norm then it should take Global.

Final T3 should be containing these fields

Modality ,Material_Code , PartType(from first table ), and CN_Norm,Global and RU_Norm which we derive based on condition from second table.

Sorry for confusion.

Hope this is better

Re: Apply Map

bharatkishore — Mon, 05 Mar 2018 15:05:48 GMT

Hi Sunny,

Anything you needed...

Please let me know