topic Re: Resolve circular loop in QlikView

Resolve circular loop

jyothish8807 — Thu, 16 Apr 2015 05:54:21 GMT

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

Re: Resolve circular loop

jagan — Thu, 16 Apr 2015 06:08:16 GMT

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.

Re: Resolve circular loop

PrashantSangle — Thu, 16 Apr 2015 06:08:52 GMT

Hi,

Just join your first last table.

Regards,

Re: Resolve circular loop

jyothish8807 — Thu, 16 Apr 2015 06:11:33 GMT

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

Regards

Re: Resolve circular loop

PrashantSangle — Thu, 16 Apr 2015 06:12:35 GMT

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

Re: Resolve circular loop

PrashantSangle — Thu, 16 Apr 2015 06:14:49 GMT

For that create key which gives you unique data.

Regards

Re: Resolve circular loop

amit_saini — Thu, 16 Apr 2015 06:31:40 GMT

KC,

Like this???

Thanks,

Re: Resolve circular loop

jyothish8807 — Thu, 16 Apr 2015 06:34:53 GMT

Hi Jagan,

Its creating multiple dates like this:

Jan 2015

Feb 2015

Mar 2015

Regards

Re: Resolve circular loop

jagan — Thu, 16 Apr 2015 06:39:21 GMT

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.

Re: Resolve circular loop

jyothish8807 — Thu, 16 Apr 2015 06:46:07 GMT

Thanks a lot jagan, i fixed it.

Regards