Can we use autogenerate and resident simultaneously?

[shivani_singhal]

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;

[Kushal_Chawda]

why you wanna do autogenerate?

[vinieme12]

What are you trying to do with the third part of your script? Table2 that is

[shivani_singhal]

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.

[shivani_singhal]

I'm trying to generate all the starting and ending dates of the months.

[Anonymous]

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

[Anonymous]

you can't use both! --> autogenerate is only needed, if you have nothing to load from like a

(resident) Table or a preceding LOAD.

[ahaahaaha]

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

[shivani_singhal]

okay. But how would I get the outstanding on UI

[shivani_singhal]

okay robin. I'll try it.