variable to calculate Total

[Anonymous]

I have columns for each month starting from Jan to Dec as below

JanAmt

FebAmt

MarAmt

...

...

DecAmt

I need to create a new column in the load script with condition

If we are in May, the TotalAmt column should be JuneAmt + JulyAmt +....DecAmt

If we are in June, the TotalAmt column should change to JulyAmt + AugAmt+...DecAmt

I need this in the load script.

[miguelbraga]

Take a special look at my script in my QVW sample:

Hope it helps you

Regards,

MB

[Anonymous]

Sorry MB.

You misunderstood the whole question. The solution that you provided has nothing to do with my question.

I have JanAmt, FebAmt...DecAmt as my columns, using which, I need to calculate a new column 'TotalAmt' based on the given conditions. Your example simply pulls data from the excel based on Month condition, nothing else.

[Anonymous]

Hi

Should i Assume janamt,febamit............in 'Description'  as string values ?

or

should  I insert some numbers in 'Descriptopm' ?

if it is numbers you can go with

rangesume(above(.....)

[miguelbraga]

Take a look at this:

Regards,

MB

[Gysbert_Wassenaar]

Perhaps like this:

LOAD

     SomeKindOfMonthField,

     JanAmt,

     FebAmt,

     MarAmt,

     ....

     DecAmt,

     Pick(SomeKindOfMonthField, 'January','February', ...etc..., 'October', 'November'),

          Rangesum(FebAmt, MarAmt, ...etc..., DecAmnt),

          Rangesum(MarAmt, ...etc..., DecAmnt),

          ....etc....

          Rangesum(NovAmt, DecAmnt),

          DecAmt

          ) as SomeAmount

FROM

     ...

     ;

[Anonymous]

Look at the file. That's what I want. You are taking the column names as values (as rows) which is not at all correct.

In the script, I need to add all the months and create a new column called 'TotalAmt' based on the below condition.

if the current month is May, the TotalAmt column should be calculated from JunAmt :

JunAmt + JulAmt + ...DecAmt

Next month, the TotalAmt should be JulAmt + AugAmt + SepAmt + ..DecAmt

We should calculate the TotalAmt column by adding remaining months excluding current month.

[miguelbraga]

Check this solution:

Regards,

MB

[Anonymous]

Still quite doesn't give the solution I needed, MB.

ID

JanAmt

FebAmt

MarAmt

AprAmt

MayAmt

JunAmt

JulAmt

AugAmt

SepAmt

OctAmt

NovAmt

DecAmt

TotalAmt

Since we are in May, the TotalAmt column should only add from JuneAmt, JulAmt..until DecAmt

Next month, when the month becomes June, the TotalAmt should calculate from JulAmt, AugAmt...DecAmt

[Anonymous]

The Source Excel MB showed is not the right data structure. Please see my Sample Excel