Connecting two tables using date key

[jegadeesan]

Hi All,

Am getting problem in retrieving rows from one table using date key of another table... This is how i made an association between tables...

Table A                          Table B                          Table C

Date,                             Product code,                  Date,

Sale,                             SAP Code ,                     Sales,

Product code,                 Brand,                            Product,

Quantity,                                                              SAP Code

Volume,

using Product code (Table A )  joined with (Table B )

using SAP code (Table B) joined with (Table C)

While I trying to see the sum of particular year sale from Table A using Date of Table C,  getting wrong sum of sale.

Can anyone help me out of this issue.....

Regards,

Jegadeesan G

[vishsaggi]

What is your data model looks like? Press ctrl+T and see if there are any synthetic keys?

Probably rename the field Date in TableC as CDate? Try this let us know or share a sample app?

[jegadeesan]

Viswa,

Aliased the same column names properly.... I guess it may because of the date columns are not properly mapped between tables... Why because am getting correct sum when i try to sum using SAP Code....

Thanks...

[agomes1971]

Hi,

why not using a calendar table?

An then concatenate

This Product code (Table A )  joined with (Table B ) with table C...

Maybe...

HTH

André Gomes

[johnw]

Your data model is circular. A joins to B joins to C joins to A (on Date). QlikView doesn't allow that. I believe it "arbitrarily" cuts one of the links. You could see which it cut by looking at the data model (Ctrl-T).

In any case, you'll need to resolve the loop. On brief glance, I don't even know why table C exists. Sales would seem to be on table A (in the form of individual sales, I gather), as is the Date. The only thing you get from table C is Product, it seems to me. So are you using SAP code to look up Product? If so, you'd only need those two fields on table C, and with only two fields, I'd load table C as a map, and then apply that map to table B to have Product in B. But I could easily be not understanding your data.

[vishsaggi]

Yes, as three tables are joining based on ProductCode and SAP Code. You might want to use calendar script to get the correct dates, however, i dont know how your dates are used here?

[saimahasan]

Your explanation seems to create a circular loop. May be this is one of the reason you are not getting correct output.

[jegadeesan]

Siddiqui,

Of course... Is there any other ways to do this... Connecting the table C thru SAP code is more important than this date field...   I have the scenario like only date field to be used in the dimension to see the sales trend of two different tables...

Thanks & Regards

Jegadeesan G

[jegadeesan]

Hi,

I tried using calendar table and it provides correct output of Table A alone....

Regards,

Jegadeesan G

[#10]

Circular References