Grouping Date

[jyothish8807]

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

[sunny_talwar]

Have you tried this may be:

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

[jyothish8807]

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

[sunny_talwar]

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

[SreeniJD]

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

[PradeepReddy]

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

[jyothish8807]

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

Regards

Jyothish KC

[sunny_talwar]

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

Best,

Sunny

[jyothish8807]

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

KC

[sunny_talwar]