Resolve circular loop

[jyothish8807]

Hi All,

I have three Tables:

Coust_ID	Date	Expense
1	12-Feb-14	100
2	11-Mar-15	221
3	23-Mar-15	245
4	1-Jun-15	334

REG_ID	Period	Year	Budget
A1	2	2015	1000
A2	1	2015	500
A3	3	2015	200
A4	2	2015	300

REG_ID	Coust_ID
A1	1
A2	2
A3	3
A4	4

And i want my output as like this:

	Jan	Feb	Mar
Expense
Budget

So when ever i try to map the dates it is forming a circular reference.Any suggestions will be really helpful. jagan‌

Regards

KC

[jagan · Accepted solution]

Hi,

Try like this

Table1:

LOAD

MonthName(Date) AS YearMonth,

CustID,

Expense

FROM Table1;

LEFT JOIN (Table1)

LOAD

*

FROM Table3;

Table2:

Noconcatenate

LOAD

REG_ID,

MonthName(MakeDate(Year, Period) ) AS YearMonth,

Budget

FROM Table2;

LEFT JOIN(Table2)

LOAD

*

FROM Table3;

Concatenate(Table1)

LOAD

*

FROM Table2;

DROP TABLE Table2;

Hope this helps you.

Regards,

Jagan.

[PrashantSangle]

Hi,

Just join your first last table.

Regards,

[jyothish8807]

Then it will create a synthetic key, when i map month from both table.

Regards

KC

[PrashantSangle]

Hi,

Try below code

Table1:

Select Coust_ID,

Date,

Expenses

from table1;

join

select RegID,

Coust_ID

from table3;

Table2:

select Reg_ID,

Period,

Year,

Budget

from Table2;

Regards

[PrashantSangle]

For that create key which gives you unique data.

Regards

[amit_saini]

KC,

Like this???

Thanks,

AS

[jyothish8807]

Hi Jagan,

Its creating multiple dates like this:

Jan 2015

Jan 2015

Jan 2015

Jan 2015

Jan 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Feb 2015

Mar 2015

Mar 2015

Mar 2015

Mar 2015

Regards

KC

[jagan]

Hi,

Try this

Hi,

Try like this

Table1:

LOAD

MonthName(MonthEnd(Date)) AS YearMonth,

CustID,

Expense

FROM Table1;

LEFT JOIN (Table1)

LOAD

*

FROM Table3;

Table2:

Noconcatenate

LOAD

REG_ID,

MonthName(MakeDate(Year, Period) ) AS YearMonth,

Budget

FROM Table2;

LEFT JOIN(Table2)

LOAD

*

FROM Table3;

Concatenate(Table1)

LOAD

*

FROM Table2;

DROP TABLE Table2;

Hope this helps you.

Regards,

Jagan.

[jyothish8807]

Thanks a lot jagan, i fixed it.

Regards

KC