Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Table:
LOAD * INLINE
[
Claim_No,Transaction_No,Paid_Date,Outstanding
123,1,1/1/17,20000
123,2,3/2/17,19000
456,3,3/1/17,15000
456,4,5/2/17,13000
123,5,8/10/17,10000
456,6,10/11/17,5000
];
Table1:
LOAD *,
min(Paid_Date) AS Start,
today() AS End
RESIDENT Table;
AUTOGENERATE 1; // I'm getting an error here.
Concatenate(Table)
Table2:
LOAD *,
if(iterno()=1,Start,floor(monthstart(addmonths(Start,iterno()-1)))) AS From,
rangemin(floor(monthend(if(iterno()=1,Start,floor(monthstart(addmonths(Start,iterno()-1)))))),End) AS To,
iterno() AS Line
RESIDENT Table1
WHILE monthstart(addmonths(Start,iterno()-1))<=End;
DROP TABLE Table1;
why you wanna do autogenerate?
What are you trying to do with the third part of your script? Table2 that is
Actually, I have a records of outstanding of some claims with their transaction no. on some dates. I want to find the outstanding on any date I want(including dates on which I don't have any record like 25th of june or other)
By using Table 2, I was trying to generate all the dates.
I'm trying to generate all the starting and ending dates of the months.
Hi Shivani
You can't use both at the same time.
and one another point i want to raise here is
you can't write your code like this with the min function.
Because min is a aggregate function and aggregate needs to group the values accordingly.
your code should look like this:
LOAD *,
min(Paid_Date) AS Start
RESIDENT Table
group by Claim_No,Transaction_No,Paid_Date,Outstanding;
Regards
Bhuvan Agarwal
you can't use both! --> autogenerate is only needed, if you have nothing to load from like a
(resident) Table or a preceding LOAD.
Hi Shivani,
If I understood your task correctly.
As variant for all possible dates. May be first create a table of necessary dates, and then join data to it?
For example,
//create all dates
Dates:
LOAD
Date(Date('01/01/2017') + RecNo() - 1) as Paid_Date
autogenerate(Today() - Date('01/01/2017') + 1);
Or
//create dates of start and end of months
Dates:
LOAD
Date(Date('01/01/2017') + RecNo() - 1) as Paid_Date
autogenerate(Today() - Date('01/01/2017') + 1)
Where (Date(Date('01/01/2017') + RecNo() - 1) = MonthStart(Date(Date('01/01/2017') + RecNo() - 1))) Or
Date(Num((Date(Date('01/01/2017') + RecNo() - 1)) + Num(Time('23:59:59.999'))) = MonthEnd(Date(Date('01/01/2017') + RecNo() - 1)));
Further
Left Join(Dates)
Table:
LOAD * INLINE
[
Claim_No,Transaction_No,Paid_Date,Outstanding
123,1,1/1/17,20000
123,2,3/2/17,19000
456,3,3/1/17,15000
456,4,5/2/17,13000
123,5,8/10/17,10000
456,6,10/11/17,5000
];
..........
Regards,
Andrey
okay. But how would I get the outstanding on UI
okay robin. I'll try it.