Current Month MTD+Previous Month Calculated YTD

[#1]

Hi All,

We are facing difficulty in implementing below logic. Please help.

Here is the logic:

For each month we are receiving the source file.

We have below source file for Aug 2016:

Entity	MonthYear	MTD	YTD
ABC	Aug-16	10	10
ABC	Aug-16	20	35
ABC	Aug-16	30	20

Source file for Sep 2016:

Entity	MonthYear	MTD	YTD
ABC	Sep-16	10	0
ABC	Sep-16	30	0
ABC	Sep-16	40	0

Output for Sep 2016:

Entity	MonthYear	MTD	YTD
ABC	Sep-16	10	20
ABC	Sep-16	30	65
ABC	Sep-16	40	60

We are not facing any issues in implementing MTD logic as it has different calculation.

But YTD logic we are not getting the desired output.

For Ex: YTD for Sep = MTD of Sep from source file + YTD of Aug month.

For the first month the logic works correctly as we are taking YTD value from source file directly. But from the next month onwards it does not works correctly and instead of taking calculated YTD value it take from source file.

Source file for OCT 2016:

Entity	MonthYear	MTD	YTD
ABC	Oct-16	40	0
ABC	Oct-16	60	0
ABC	Oct-16	80	0

Output for Oct 2016:

Entity	MonthYear	MTD	YTD
ABC	Oct-16	40	60
ABC	Oct-16	60	125
ABC	Oct-16	80	140

Please help in implementing it. Any help is much appreciated!!!

Regards

Deepak

[sunny_talwar]

Would you be able to share a sample file where you are working on this?

[#3]

Hi Sunny,

Not able to share ....Sorry

Regards

Deepak

[sunny_talwar]

okay

[petter]

To get the logic to work properly we need to generate a unique row id so we can do a lookup into previous loaded rows using the Lookup() function in the script. This unique row id must be persisted for the table as a new field as long as the script runs.

[#6]

Thanks Petter for reply!!

What exactly lookup function do here ??

For the Unique we have one more Column which is Unqiue i.e GLAccount we can use that column for the Unique ID than there is no use of Recno() function.??

Regards

Deepak

[petter]

Yes you are right - you should use a column that already is unique instead of the synthetically produced UniqueID that I made - then you will not need to use the RecNo() function.

Lookup() is to find the old YTD value from previously loaded files or previously loaded values for the same field in the current load statement.

[#8]

Hi Petter,

Thank You for the help!!!

Above code works fine for first two months. The moment I add other month data, YTD logic does not works fine. Currently we have loaded three month data  you can find as attachment...

Aug month YTD displayed as blank.

Attached here the source file for three Months and .qvw file.

Thanks a lot for the support!!!

Regards,

Deepak

[petter]

Make sure that your files are named so they are processed in the right order.

The logic in the load script demands that the ABC* files come in ascending order when it comes to file names.

So you should have files being named like:

ABC01 - for January

....

ABC12 - for December

Just renaming your file ABC1.txt to DATA-08.txt and ABC2.txt to DATA-07.txt will make the load script work without a problem. I just tested it.

[#10]

Hi Peter,

Thanks for Reply !!

Since we have data for last 5 years that's why we have used ABC * for loading the files because we want dynamic load for every month.

Regards

Deepak