Manage Conditional Expressions

[Anonymous]

Hi,
I have a straight table with the below 3 columns loaded from SQL:

COST	BILL DAY	ALERT DATE
10	20	25-MAY
20	8	26-MAY
30	25	20-MAY

I want to add additional 3 columns in the STRAIGHT table. Could you please let me know how this can be achieved?

COST	BILL DAY	ALERT DATE	NEXT BILL DATE	DAYS FROM ALERT TO NEXT BILL + 5	TOTAL
10	20	25-MAY	20-JUN	31	310
20	8	26-MAY	8-JUN	18	360
30	25	20-MAY	25-MAY	10	300

the logic for NEXT BILL DATE should be based on BILL DAY and ALERT DATE.

For Ex:

If bill day is 20 and alert is on 25th of month, then bill day for next month should be populated(Example 1)

If bill day is 25 and alert is before that, then next bill date would be for current month(Example 3)

Days from Alert to Next bill should be:

COLUMN4 - COLUMN3 +5

Total will be :

COLUMN5*COLUMN1

Could you please let me know how to implement the above?

Thanks

Taher

[vishsaggi]

Try this?

BillDay:

LOAD *, Day(NextBillDateID - ALERTDtID) + 6 AS DaysToNextBill,

        (Day(NextBillDateID - ALERTDtID) + 6) * COST AS Total;

LOAD *, Date(ALERTDtID, 'DD-MMM') AS DayMonth,

        IF(Day(ALERTDtID) >=25, Date(MonthEnd(ALERTDtID) + BILLDAY),

                                Date(ALERTDtID + (BILLDAY - Day(ALERTDtID)))) AS NextBillDateID,

        IF(Day(ALERTDtID) >=25, Date(MonthEnd(ALERTDtID) + BILLDAY,'DD-MMM'),

                                Date(ALERTDtID + (BILLDAY - Day(ALERTDtID)), 'DD-MMM')) AS NextBillDate;

LOAD *, MakeDate(Year(Today()), Month(Date#(ALERTDATE, 'DD-MMM')), Left(ALERTDATE,2)) AS ALERTDtID INLINE [

COST, BILLDAY, ALERTDATE

10, 20, 25-MAY

20, 8, 26-MAY

30, 25, 20-MAY

];