topic Re: Connecting two tables using date key in QlikView

Connecting two tables using date key

jegadeesan — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Connecting two tables using date key

vishsaggi — Thu, 29 Dec 2016 17:51:45 GMT

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?

Re: Connecting two tables using date key

jegadeesan — Thu, 29 Dec 2016 18:01:58 GMT

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...

Re: Connecting two tables using date key

agomes1971 — Thu, 29 Dec 2016 18:14:20 GMT

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

Re: Connecting two tables using date key

johnw — Thu, 29 Dec 2016 18:49:33 GMT

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.

Re: Connecting two tables using date key

vishsaggi — Thu, 29 Dec 2016 18:52:29 GMT

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?

Re: Connecting two tables using date key

saimahasan — Mon, 02 Jan 2017 11:50:17 GMT

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

Re: Connecting two tables using date key

jegadeesan — Mon, 02 Jan 2017 12:09:04 GMT

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

Re: Connecting two tables using date key

jegadeesan — Mon, 02 Jan 2017 12:12:14 GMT

Hi,

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

Regards,

Jegadeesan G

Re: Connecting two tables using date key

Mon, 02 Jan 2017 12:21:34 GMT

Circular References

Re: Connecting two tables using date key

satheshreddy — Mon, 02 Jan 2017 12:22:45 GMT

Hi Jagadeesan,

your getting wrong some why because, your data having two sales columns 1 is sales and 2 nd one is sale first you have to check data model if you are written

1). sum(sales) you will get Table C sales field sum.

2). sum(sale) you will getting Table1 Sale sum

please check and change the names same you will get correct sum.

Regards
Sathsih

Re: Connecting two tables using date key

jegadeesan — Mon, 02 Jan 2017 13:07:40 GMT

Hi sathish...

Table A has internal sales and data table C has third party vendor provided sales data to the same company..

Am trying to ensure the data provided by third party vendor is in line with Internal sales data.... Calculation is like Vendor sales data / Internal sales data.... That i have to show in Trend line... am in the situation to use only one date dimension either from Table A or Table C.... When I am using table A Date dimension, internal sales correct.... It happens vice versa to table C as well.... Table C source file is in CSV format...

Regards,

Jegadeesan G

Re: Connecting two tables using date key

satheshreddy — Mon, 02 Jan 2017 13:16:55 GMT

Hi Jaga,

so you can create Calendar and perform the options.

Regards

Sathish

Re: Connecting two tables using date key

kumarravi — Thu, 31 Mar 2022 09:40:25 GMT

Hey ,

(For reference I will use only 2 tables of yours, it is very similar for rest)

You can follow the following steps to do this :

Step 1 : Create Date, month, year column using Date column in each table

Posting Table:

Date([Date]) as Posting_date,

Month(Date([Date])) as Posting_month,

Year(Date([Date])) as Posting_year

Expense Table :

Date([Voucher Date]) as Expense_date,

Month(Date([Voucher Date])) as Expense_month,

Year(Date([Voucher Date])) as Expense_year

Step 2: Create a Link Table

Link_table_1 :

Load Distinct

Posting_date as Date,

Posting_month as Month,

Posting_year as Year

Resident [Posting table];

Concatenate Link_table_1

Load

Expense_date as Date,

Expense_month as Month,

Expense_year as Year

Resident [Expense table];

NoConcatenate

Step 3: Create a new table and drop the older one

LINK_TABLE:

Load

Date,

Month,

Year

Resident Link_table_1;

Drop Link_table_1;