topic Re: Grouping Date in QlikView

Grouping Date

jyothish8807 — Tue, 29 Sep 2015 18:30:40 GMT

Hi All,

I need your help in finding an approach to achieve my requirement.

I have 3 flat file, one gets updated on monthly basis, one weekly and one file daily.

So i am capturing the fileupdate date using monthstart for monthly file, weekstart for weekly file and as a normal date for daily file.

My idea is to group this date to month for selection. Suppose if i select Sep all the max dates which falls in sep should be selected.

Now my challenge is when i try to find the max date, i always get the fileupdate date of daily file, since it is getting updated daily and have max date.

So now if write a expression like:

sum(Date={'$(=max(Date))"},Flag={'Monthly File'}>}Salary)

In this case in max(Date) i am getting the date of daily file and not the monthly file so the output is wrong.

Is there any way to achieve my requirement?

For max date can we create a variable which will change according to the flag i am using?

Any suggestion will be really helpful.

Only solution i have is to load all the sources daily to keep the date consistent but it will increase the data size which i am trying to avoid as of now.

Regards

Jyothish KC

Re: Grouping Date

sunny_talwar — Tue, 29 Sep 2015 20:16:47 GMT

Have you tried this may be:

=Sum(Date={"$(=Date(Max({<Flag={'Monthly File'}>} Date), 'YourDateFieldFormat'))"}>}Salary)

Re: Grouping Date

jyothish8807 — Wed, 30 Sep 2015 04:43:50 GMT

Hi Sunny,

Thanks for your suggestion, but in my case i have multiple flags (Datasoursre) and i want to come up with one variable which works for all 3 data source.

Regards

Jyothish KC

Re: Grouping Date

sunny_talwar — Wed, 30 Sep 2015 04:56:07 GMT

I guess have a list box where you have always one value selected. For example:

File:

LOAD * Inline [

File

Monthly File

Weekly File

Daily File

];

then set a variable vFlag = Only(File) and change your expression to be this:

=Sum(Date={"$(=Date(Max({<Flag={'$(vFlag)'}>} Date), 'YourDateFieldFormat'))"}>}Salary)

You might have to play around the correct syntax within the set analysis, but the idea is that once you make a selection in File list box (only one selection at a time), you will see different Flags.

I think this is what you are looking for. If not please elaborate your requirement and attach a sample with expected output.

Best,

Sunny

Re: Grouping Date

SreeniJD — Wed, 30 Sep 2015 05:01:45 GMT

Hi Jyothish,

I have a suggestion, you can also update a flag for Monthly load and weekly load and daily load on each and every day to resolve this issue.

HTH

Sreeni

Re: Grouping Date

PradeepReddy — Wed, 30 Sep 2015 05:22:01 GMT

can you share the sample application, and expected output..

Re: Grouping Date

jyothish8807 — Wed, 30 Sep 2015 10:03:14 GMT

Thanks for the suggestion Sunny, but i cant use this approach in my situation.

Regards

Jyothish KC

Re: Grouping Date

sunny_talwar — Wed, 30 Sep 2015 10:06:57 GMT

May be if you elaborate or share a sample, we can help you better.

Best,

Sunny

Re: Grouping Date

jyothish8807 — Wed, 30 Sep 2015 10:14:14 GMT

Yes Jd , it is a solution as i mentioned above in my question also, but the performance impact will be high since i am duplicating same data again and again which comes monthly and i want to avoid this.

Thanks for your help.

Regards

Re: Grouping Date

sunny_talwar — Wed, 30 Sep 2015 10:15:53 GMT

Re: Grouping Date

jyothish8807 — Wed, 30 Sep 2015 10:18:55 GMT

Hello All,

I have tried to change my logic.

Now what i am trying to do is:

Suppose Monthly source get loaded for 3 months then it will have dates 1 Mar 2015,1 Apr 2015 and 1 May 2015.

Weekly will have 4 dates for each month and daily will have 30 or 31 dates in each month.

I need your help to write a where clause from which i can extract only the max(Date) for each source and for each month also.

So for monthly files when i apply the where clause it should get 1 Mar 2015,1 Apr 2015 and 1 May 2015 as output, but for weekly file only the last week date should be taken for all the months and the same for daily file.

Can any one please help me with this approach?

Thanks

Re: Grouping Date

jyothish8807 — Wed, 30 Sep 2015 10:20:25 GMT

Re: Grouping Date

SreeniJD — Wed, 30 Sep 2015 10:26:12 GMT

Hi Jyothish,

Try this..

Have a data source number Monthly =1, Weekly=2, Daily =3 and in your expression,

sum(Date={'$(=max(Date,<datasource=3>))"},Flag={'Monthly File'}>}Salary)??????

HTH

Sreeni

Re: Grouping Date

jyothish8807 — Wed, 30 Sep 2015 10:36:40 GMT

The jd, again this approach i have to mention to group <datasource=3> and have to create multiple variables for different data source which i don't want to.

Now i am trying a now logic and need your help again for this hope you don't mind.