topic Re: Find Max date of the year based other 2 fields(Name & Design) in QlikView

Find Max date of the year based other 2 fields(Name & Design)

Thu, 01 Sep 2011 12:57:41 GMT

Hi All,

Herewith i have attached the QVW file which is developed using QV10 Personal Edition.

As there can be more than one Appraisal per year and Design(Trainer, Junior & Senior Teacher and Head Master), we only want to one Appraisal per year and this should be the lasted Appraisal per year.

Actual One : Desired One

The script level solution should be most useful.

Thanks ....

Be Blessed with Happiness!!!

Ragards

Gangadharan Moorthy

Find Max date of the year based other 2 fields(Name & Design)

swuehl — Sun, 04 Sep 2011 23:20:42 GMT

Hi,

this will restrict your data like desired.

Master:

LOAD Record_No,

Name,

DOJ,

[Appraisal Date],

Salary,

Design,

Login_ID

FROM

FindMaxDate.xls

(biff, embedded labels, table is Sheet1$);

Filtered:

right join (Master) LOAD Name, Design, Date(max([Appraisal Date])) as [Appraisal Date]

resident Master group by Name, Design, year([Appraisal Date]);

If you want to keep all data of all dates in your data model, you could use a left join and create a flag to indicate the dates you are interested in:

Filtered:

left join (Master) LOAD Name, Design, Date(max([Appraisal Date])) as [Appraisal Date], '1' as flag

resident Master group by Name, Design, year([Appraisal Date]);

Hope this helps,

Stefan

Find Max date of the year based other 2 fields(Name & Design)

Mon, 05 Sep 2011 06:36:39 GMT

Thanks a lot....

If the script is like this, its working good.

Filtered:

LOAD Name, Design, Date(max([Appraisal Date])) as [Appraisal Date], '1' as flag

resident Master group by Name, Design, year([Appraisal Date]);

Drop table Master;

But the below scripts throughs some Error like: "Execution of script failed. Reload old script."

Filtered:

left join (Master) LOAD Name, Design, Date(max([Appraisal Date])) as [Appraisal Date], '1' as flag

resident Master group by Name, Design, year([Appraisal Date]);

Re: Find Max date of the year based other 2 fields(Name & Design)

Mon, 05 Sep 2011 08:01:44 GMT

Ich werde ab 05.09.2011 nicht im Büro sein. Ich kehre zurück am

23.09.2011.

Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Verwertung des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.

Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

Re: Find Max date of the year based other 2 fields(Name & Design)

Mon, 05 Sep 2011 08:01:44 GMT

Ich werde ab 05.09.2011 nicht im Büro sein. Ich kehre zurück am

23.09.2011.

Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

Find Max date of the year based other 2 fields(Name & Design)

swuehl — Mon, 05 Sep 2011 09:55:21 GMT

Hi,

the exact script worked at my place. Could you send your updated app again and I will have a look. If you leave out the left join, you should get a syn Key Table, do you?

Stefan

Re: Find Max date of the year based other 2 fields(Name & Design)

Mon, 05 Sep 2011 14:02:52 GMT

Ich werde ab 05.09.2011 nicht im Büro sein. Ich kehre zurück am

23.09.2011.

Ich werde Ihre Nachrichten nach meiner Rückkehr beantworten.

Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank!

Re: Find Max date of the year based other 2 fields(Name & Design)

Tue, 06 Sep 2011 11:24:52 GMT

Hi Stefan,

Can you please tell me how to forward modified one to you? Bcz here i can't upload.

Thanks

Gangadharan

Find Max date of the year based other 2 fields(Name & Design)

swuehl — Tue, 06 Sep 2011 12:08:35 GMT

Hi Gangadharan,

there is an upload option in advanced editor (go to edit / answer, "use advanced editor" on the top right ).

Stefan

Re: Find Max date of the year based other 2 fields(Name & Design)

Tue, 06 Sep 2011 14:40:03 GMT

Thanks Stepan,

I have uploaded the modified qvw.

Hopefully waiting for your reply on this.

Gangadharan

Re: Find Max date of the year based other 2 fields(Name & Design)

swuehl — Tue, 06 Sep 2011 15:13:15 GMT

Hi Gangadharan,

I think there were only a

drop table Master;

too much. I checked both versions and they are working, Please check attached file. You could switch between two version by commenting in /out in script.

Regards,

Stefan