Solved: Re: Resolve circular loop - Qlik Community

jyothish8807 · ‎2015-04-16

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

Best Regards,
KC

jagan · ‎2015-04-16

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.

View solution in original post

jagan · ‎2015-04-16

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 · ‎2015-04-16

Hi,

Just join your first last table.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

jyothish8807 · ‎2015-04-16

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

Regards

KC

Best Regards,
KC

PrashantSangle · ‎2015-04-16

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

PrashantSangle · ‎2015-04-16

For that create key which gives you unique data.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

amit_saini · ‎2015-04-16

KC,

Like this???

Thanks,

AS

jyothish8807 · ‎2015-04-16

Hi Jagan,

Its creating multiple dates like this:

Jan 2015

Feb 2015

Mar 2015

Regards

KC

Best Regards,
KC

jagan · ‎2015-04-16

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 · ‎2015-04-16

Thanks a lot jagan, i fixed it.

Regards

KC

Best Regards,
KC