Need to get 2 ages

[bharatkishore]

HI All,

Frank Hartmann

I have the following code:

load

System_Code ,

//   SystemCodeDescription as SCD,

  Country ,

  Market ,

  Equipment ,

      Calendar_Month_Code ,

      Plant ,

      District ,

      [Part Group] ,

      Entitlement ,

      Key_EMS ,

      Install_Flag ,

      Removal_Flag ,

  TechnicalStartDate ,

//   Age2 as AGE2,

  Age_Final ,

  Part_Age_Bucket

Now to get the minimum age i will write this expression

min(Age_Final ) as Age_Final  and do group by. for this i will get one minimum age which is fine.

But i need to get 2 min ages.

Can you please help me.

Thanks,

Bharat

[bharatkishore]

PFA qvw.And excel as well.

Please find below image

Instead of 7 i need 5 there in E3 equipment.. if this is done everything will be done.

kindly help..

[vamsee]

Can you please explain the logic behind considering Key 11 instead of Key 12, I mean why 5?

Also, can you please confirm if you want the Field Key_EMS in your final output.

[bharatkishore]

Because I need to consider 2 minimum values. . Even though the values are same as you can see In the excel I have 5,5,7 .. I need to consider two minimum values even though the numbers are same.  So that is why  I need to get 5,5 only.

Key_EMS is not required in the final output.

Please let me know if you need anything more .

[vamsee]

PFA and let me know if it works for you.

Note: Added a column called Flag to differentiate the rows.

[bharatkishore]

Sorry Vamsee for all i am getting the same output. But the output should be in below format:

Equipment	Part Group	Key_EMS	Calendar	Age_Final	Quantity
E1	P1	Key2	2013-01	3	0
E1	P1	Key4	2013-01	5	1
E2	P1	Key7	2013-01	22	1
E2	P1	Key8	2013-01	15	0
E3	P2	Key10	2013-02	5	1
E3	P2	Key11	2013-02	5	0

But  i am getting as below:(it is considering only same age but i need to 2 min ages)

Please let me know if you need anything more.

[sasiparupudi1]

Pl Try

Data:

LOAD

Equipment,

[Part Group],

Key_EMS,

Calendar,

Age_Final,

Quantity

FROM

(ooxml, embedded labels, table is Sheet1);

MinData:

NoConcatenate Load

Equipment,

[Part Group],

Calendar,

Concat(Age_Final,'|',Age_Final) as Age_Final ,

Concat(Key_EMS,'|',Age_Final) as Key_EMS,

Concat(Quantity,'|',Age_Final) as Quantity

Resident Data

Group By

Equipment,

[Part Group],

Calendar

;

Final:

NoConcatenate Load

Equipment,

[Part Group],

Calendar,

SubField(Age_Final,'|',1) As Age_Final,

SubField(Key_EMS,'|',1) As Key_EMS,

SubField(Quantity,'|',1) As Quantity

Resident MinData

;

Concatenate(Final)

Load

Equipment,

[Part Group],

Calendar,

SubField(Age_Final,'|',2) As Age_Final,

SubField(Key_EMS,'|',2) As Key_EMS,

SubField(Quantity,'|',2) As Quantity

Resident MinData

;

Drop Table MinData;

Drop Table Data;     

[bharatkishore]

Thank you Sir.. I have no words...Just Hatsoff..I thought this is not possible. Need sometime to understand your code

One final thing.. When i change my data to below format:

E3	P1	Key13	2013-03	10	1
E3	P1	Key14	2013-03	10	0
E3	P1	Key15	2013-03	10	0

I mean when ages are same i am getting 2  min ages but i am getting quantity as 0. Below image for reference:

I need to get quantity where it is 1 and need to get 2 min ages(which i am getting now). This is the final one. Pls help..

[sasiparupudi1 · Accepted solution]

May be Try

Data:

LOAD

Equipment,

[Part Group],

Key_EMS,

Calendar,

Age_Final,

Quantity

FROM

(ooxml, embedded labels, table is Sheet1);

Map_Qty:

Mapping Load

Equipment&'|'&[Part Group]&'|'&Key_EMS&'|'&Calendar As Key,

Quantity

Resident Data

;

MinData:

NoConcatenate Load

Equipment,

[Part Group],

Calendar,

Concat(Age_Final,'|',Age_Final) as Age_Final ,

Concat(Key_EMS,'|',Age_Final) as Key_EMS,

Concat(Quantity,'|',Age_Final) as Quantity

Resident Data

Group By

Equipment,

[Part Group],

Calendar

;

Transformed:

NoConcatenate Load

Equipment,

[Part Group],

Calendar,

SubField(Age_Final,'|',1) As Age_Final,

SubField(Key_EMS,'|',1) As Key_EMS,

SubField(Quantity,'|',1) As Quantity

Resident MinData

;

Concatenate(Transformed)

Load

Equipment,

[Part Group],

Calendar,

SubField(Age_Final,'|',2) As Age_Final,

SubField(Key_EMS,'|',2) As Key_EMS,

SubField(Quantity,'|',2) As Quantity

Resident MinData

;

Drop Table MinData;

Drop Table Data;     

Final:

NoConcatenate Load

*,

ApplyMap('Map_Qty',Equipment&'|'&[Part Group]&'|'&Key_EMS&'|'&Calendar,'NA') as NewQty

Resident Transformed;

Drop Table Transformed;

[bharatkishore]

Thank you so much sir.....You  just solved my 5 day problem... thank you so much... really thank you....No words...Really thank you.......