topic Re: Need to get 2 ages in QlikView

Need to get 2 ages

bharatkishore — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Need to get 2 ages

Anil_Babu_Samineni — Thu, 20 Sep 2018 13:46:16 GMT

You want this in script?

Re: Need to get 2 ages

bharatkishore — Thu, 20 Sep 2018 13:48:23 GMT

Yes Anil.. Can you please help..

Re: Need to get 2 ages

Anil_Babu_Samineni — Thu, 20 Sep 2018 14:08:59 GMT

If possible, Please share QVD?

Re: Need to get 2 ages

bharatkishore — Thu, 20 Sep 2018 14:11:40 GMT

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.

Re: Need to get 2 ages

Anil_Babu_Samineni — Sat, 22 Sep 2018 06:11:53 GMT

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

Re: Need to get 2 ages

bharatkishore — Mon, 24 Sep 2018 11:09:38 GMT

Hi Anil,

Kindly find attached qvw and excel.

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

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

Re: Need to get 2 ages

Anil_Babu_Samineni — Tue, 25 Sep 2018 04:34:25 GMT

PFA

Re: Need to get 2 ages

bharatkishore — Tue, 25 Sep 2018 04:45:20 GMT

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

Re: Need to get 2 ages

bharatkishore — Tue, 25 Sep 2018 06:48:48 GMT

HI tresesco‌..

Can you please help..

Re: Need to get 2 ages

bharatkishore — Tue, 25 Sep 2018 10:41:38 GMT

Hi Anil,

Any help pls..

Re: Need to get 2 ages

sasiparupudi1 — Tue, 25 Sep 2018 10:50:25 GMT

May be try like below

Data:

LOAD Equipment,

[Part Group],

Key_EMS,

Calendar,

Age_Final,

Quantity

FROM

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

Load

Equipment,

[Part Group],

Calendar,

Sum(Quantity) as TotalQty

Resident Data

Group By

Equipment,

[Part Group],

Calendar

;

Left Join(Data)

Load

Equipment,

[Part Group],

Calendar,

Min(TotalQty,1) as Min1,

Min(TotalQty,2) as Min2

Resident Data

Group By

Equipment,

[Part Group],

Calendar

;

Re: Need to get 2 ages

bharatkishore — Tue, 25 Sep 2018 11:16:35 GMT

Thanks For your reply.. But not getting the desired output

Re: Need to get 2 ages

sasiparupudi1 — Tue, 25 Sep 2018 11:18:02 GMT

May I know the desired o/p?

Re: Need to get 2 ages

bharatkishore — Tue, 25 Sep 2018 11:31:16 GMT

Output should be in the 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

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

Re: Need to get 2 ages

sasiparupudi1 — Tue, 25 Sep 2018 12:08:09 GMT

Try

Data:

LOAD Equipment,

[Part Group],

Key_EMS,

Calendar,

Age_Final,

Quantity

FROM

(ooxml, embedded labels, table is Sheet1);

MinData:

Load

Equipment,

[Part Group],

Calendar,

Min(Age_Final,1) as Age_Final

Resident Data

Group By

Equipment,

[Part Group],

Calendar

;

concatenate( MinData)

Load

Equipment,

[Part Group],

Calendar,

Min(Age_Final,2) as Age_Final

Resident Data

Group By

Equipment,

[Part Group],

Calendar

;

Left Join(MinData)

LOAD Equipment,

[Part Group],

Key_EMS,

Calendar,

Age_Final,

Quantity

Resident Data;

Drop Table Data;

Re: Need to get 2 ages

bharatkishore — Tue, 25 Sep 2018 12:25:59 GMT

Thank you so much for your time and reply. Only thing i need to get is that please find the below image:

Here for equipment E3 i am getting 3 age final.. where only i need to get 2 ages. which means it should be

E3	P2	Key10	2013-02	5	1
E3	P2	Key11	2013-02	5	0

If this is done then hopefully it will be done. Kindly help.

Please let me know if you need anything more.

Re: Need to get 2 ages

bharatkishore — Tue, 25 Sep 2018 13:42:02 GMT

HI Sasidhar,

Any suggestion pls..

Re: Need to get 2 ages

bharatkishore — Tue, 25 Sep 2018 17:23:04 GMT

vamsee99999‌ can you please have a look and help..

Re: Need to get 2 ages

vamsee — Tue, 25 Sep 2018 17:38:00 GMT

Can you please share your final QVW after the progress you've achieved with the above suggestions.