topic circular reference in QlikView

circular reference

Thu, 21 Jan 2010 11:24:53 GMT

Hi,

I have a problem with a circular reference.

I have 4 tables:

Invoices

---------

ItemNum,CustNum,InvDate,Amount

Receptions

-------------

ItemNumRec,DelvDate,Quantity

Date

------

Year,Month,Day,InvDate,DelvDate

Items

------

ItemNum,ItemNumRec,ItemDesc

How do I solve my problem? For example when I select a Year I want to see all the invoices linked to that year and all the Receptions linked to that year.

But for item the same issue.

Can someone please help me how to solve this?

circular reference

spsrk_84 — Thu, 21 Jan 2010 12:33:29 GMT

Hi,

Is the Inv date in Date table and in Invoices table are similar?

And if possible can u attach the file with minium data ..

Regards,

Ajay

circular reference

Thu, 21 Jan 2010 12:45:25 GMT

Yes they are simular because the date table is generated in qlikview through following script.:

Date_src:

LOAD

$(S) + rowno() - 1 as

DateID

AUTOGENERATE

NumOfDays;

//Build the date dimension

Date:

LOAD

DateID

as InvDate

DateID

as DelvDate

date

(DateID,'DD.MM.YYYY') as FULLDate

day

(DateID) as Day

week

(DateID)as Week

weekday

(DateID + 2) AS Weekday

month

(DateID) as Month

year

(DateID)&'/'&Month(DateID) as MonthYear

year

(DateID)&if(len(num(month(DateID)))=1, '0'&num(month(DateID)) , num(month(DateID))) as MonthYearNum

'Q'

& ceil(month(DateID) / 3) AS Quarter

year

(DateID) as Year

month

(DateID) & ' - ' & year(DateID) as Period

// Year flags

inyear(DateID, Today(), 0) * -1 AS CY

// Year-to-date flags

inyeartodate(DateID, Today(), 0) * -1 AS CYTD

// Quarter flags

inquarter(DateID, Today(), 0) * -1 AS CQ

// Quarter-to-date flags

inquartertodate(DateID, Today(), 0) * -1 AS CQTD

// Month flags

inmonth(DateID, Today(), 0) * -1 AS CM

inmonth

(DateID, Today(), -1) * -1 AS PM

Year

(DateID) & '- ' & Right('0'&num(Month(DateID)),2) as

RollingMonth

RESIDENT

Date_src;

circular reference

Thu, 21 Jan 2010 12:45:27 GMT

Yes they are simular because the date table is generated in qlikview through following script.:

Date_src:

LOAD

$(S) + rowno() - 1 as

DateID

AUTOGENERATE

NumOfDays;

//Build the date dimension

Date:

LOAD

DateID

as InvDate

DateID

as DelvDate

date

(DateID,'DD.MM.YYYY') as FULLDate

day

(DateID) as Day

week

(DateID)as Week

weekday

(DateID + 2) AS Weekday

month

(DateID) as Month

year

(DateID)&'/'&Month(DateID) as MonthYear

year

(DateID)&if(len(num(month(DateID)))=1, '0'&num(month(DateID)) , num(month(DateID))) as MonthYearNum

'Q'

& ceil(month(DateID) / 3) AS Quarter

year

(DateID) as Year

month

(DateID) & ' - ' & year(DateID) as Period

// Year flags

inyear(DateID, Today(), 0) * -1 AS CY

// Year-to-date flags

inyeartodate(DateID, Today(), 0) * -1 AS CYTD

// Quarter flags

inquarter(DateID, Today(), 0) * -1 AS CQ

// Quarter-to-date flags

inquartertodate(DateID, Today(), 0) * -1 AS CQTD

// Month flags

inmonth(DateID, Today(), 0) * -1 AS CM

inmonth

(DateID, Today(), -1) * -1 AS PM

Year

(DateID) & '- ' & Right('0'&num(Month(DateID)),2) as

RollingMonth

RESIDENT

Date_src;

circular reference

spsrk_84 — Thu, 21 Jan 2010 13:05:53 GMT

Hi,

Plz attach the sample data file then it will be easy ...........

circular reference

Thu, 21 Jan 2010 13:08:54 GMT

I can't attach a file because of very confidential data.

circular reference

spsrk_84 — Thu, 21 Jan 2010 13:37:09 GMT

Hi,

I am attaching the file please find the attached and go through the script ,in the earlier scenario circular loop is there now i elminated that loop check whether the data what u r getting is appropriate or not.

Rgds,

Ajay

circular reference

Thu, 21 Jan 2010 13:41:55 GMT

The loop is gone but my link through item also.

I still need to have that connection because when I select one item I want to see invoices and receptions for that item.