Solved: Need to get 2 ages - Qlik Community

bharatkishore · ‎2018-09-20

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

sasiparupudi1 · ‎2018-09-27

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;

View solution in original post

Anil_Babu_Samineni · ‎2018-09-20

You want this in script?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

bharatkishore · ‎2018-09-20

Yes Anil.. Can you please help..

Anil_Babu_Samineni · ‎2018-09-20

If possible, Please share QVD?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

bharatkishore · ‎2018-09-20

PFA qvw

Now if you see in the chart we have age final there the number 84 is coming 5 times but i needed only twice.

Like if you see if 2014-09 you can see in age final i am getting 116,1,116,116. But out of those 4 i need to get only 2 records i.e. 1,116.

the same in calendar month code in 205-11 i am getting 15,11,130.. there i should get min 2 i.e. 15 and 11 only should come.

In case if there are no filters in equipment pls filter 531926 and part group as MRC 0508.

Please let me know if you need anything more.

Anil_Babu_Samineni · ‎2018-09-22

Difficult to know your business, Unless Make copy with sample data set to find better.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

bharatkishore · ‎2018-09-24

Hi Anil,

Kindly find attached qvw and excel.

now in Age_Final i have the following output: and image for reference:

10

5

10

3

Now i need to get only min ages i.e. 3 and 5. I need to this in script.

Anil_Babu_Samineni · ‎2018-09-25

PFA

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

bharatkishore · ‎2018-09-25

Thank you so much Anil. But just needed one thing needed

, Kindly find the attached excel.

Now here the i have added quantity column and some more equipments and part groups and keys and the output should be:

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

And need to do sum of quantity and group by should be on following columns i.e Equipment,Part Group,Calendar

Please let me know if you need anything more.

Kindly help

bharatkishore · ‎2018-09-25

HI tresesco‌..

Can you please help..