circular reference

[#1]

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?

[spsrk_84]

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

[#3]

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

,

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;

[#4]

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

,

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;

[spsrk_84]

Hi,

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

[#6]

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

[spsrk_84]

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

[#8]

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.