topic Re: Refer to a value from another Straight Table in QlikView

Refer to a value from another Straight Table

Thu, 27 Feb 2014 02:40:14 GMT

Hi:

I am new to Qlikiview help anyone can help me with this.

Currently I have two Straight Tables with two different Dimensions.

Let call the first table as Actual Settlement.

I use Settlement Date as the Dimension and use a variable to calculate the sum of Settlement by each month.

This field is the same for each Settlement Date within the same month.

Like this:

SettlementDate DailySettlement CumulativeSettlement TotalSettlementbyMonth

30/12/2013 5 75 80

31/12/2013 5 80 80

1/1/2014 4 4 100

2/1/2014 5 9 100

3/1/2014 10 19 100

4/1/2014 7 16 100

31/1/2014 14 100 100

The second table is called Forecast Settlement.

The Dimension for this table is the Reload Time which is set to be the first day of each month.

The ForecastSettlement is also a variable.

AsOfDate (Some other variables) ForecastSettlement

1/10/2013 (Some other variables) 90

1/11/2013 (Some other variables) 110

1/12/2013 (Some other variables) 120

1/1/2014 (Some other variables) 100

1/2/2014 (Some other variables) 100

My question is it possible to

add a new column in the second table and refer to the TotalSettlementbyMonth number from the first table

and add a new column in the first table refer to the ForecastSettlement number from the second table?

Thank you very much for your time.

Re: Refer to a value from another Straight Table

Thu, 27 Feb 2014 17:21:01 GMT

Hi Baiking,

I'm not totally clear on your request, perhaps a sample qvw would help. It sounds like you want to display data from two unlinked tables using their respective date fields. There is a way to do this using 'sum(if' formula. See attached.

Hope this helps.

Matt

Re: Refer to a value from another Straight Table

Jason_Michaelides — Thu, 27 Feb 2014 17:24:09 GMT

A sample QVW would definitely help here. We don't know if the two tables are linked at all in the data model?

Re: Refer to a value from another Straight Table

Sun, 02 Mar 2014 22:20:15 GMT

Hi Matt:

Thank you very much for your reply.

I sorry that I cannot open your file because I am using a desktop version.

Yes, they are unlinked tables.

Some how I linked them and it seems like it worked.

But I also want to know your method for unlinked tables.

Is it possible for you to demonstrate how to do it without the qvw file?

Thank you very much.

Re: Refer to a value from another Straight Table

Sun, 02 Mar 2014 22:21:01 GMT

Hi Jason,

No, the tables are not linked with each other.

Cheers,

Re: Refer to a value from another Straight Table

Sun, 02 Mar 2014 23:18:50 GMT

Hi,

You can create a new field in the Actual Settlement Table with the YearMonth value of the Date value in SettlementDate.

For it you an use MonthStart(SettlementDate) in load script.

Then you can link both tables by MonthStart(SettlementDate) field and AsOfDate in Forecast Table.

At this point you have the both tables linked.

If you want to add a new column in the second table and refer to the TotalSettlementbyMonth number from the first table: you can use sum(TotalSettlementbyMonth),

And if you want to add a new column in the first table refer to the ForecastSettlement number from the second table... here I dont understand very well the requirement, if you have a number for a one month, you dont know who distribute this value in date granularity to show this in the first table. You need have data in date granularity.

Can you upload your qvw?

Please tell me if you have questions and to answer my last question about granularity data.

Regards.

Re: Refer to a value from another Straight Table

stevedark — Sun, 02 Mar 2014 23:25:12 GMT

As you discovered, naming the date field the same in both tables will cause an association and you can then use the date as a dimension and include expressions from both source tables in the one presentation table.

When other fields start also associating though things start going a bit wrong.

What I always recommend is concatenating all 'event' type data into a single table, matching as many field names as possible (eg. the date).

To get a single Event Date you would need to rename fields on load, eg. for the first table:

Events:

LOAD
SettlementDate as [Event Date],

SettlementDate,

null() as AsOfDate,

And then for the second table:

CONCATENATE(Events)

LOAD

AsOfDate as [Event Date],

null() as SettlementDate,

AsOfDate,

The CONCATENATE statement ensures that there is only one Events table - Google for "QlikView Concatenate" for more information.

Once you have the concatenated table then all is very straight forward. You can have Event Date as the dimension and have calculations based on all the other fields without problems.

Without concatenation or association things are much less efficient, but a statements like this should work, assuming that SettlementDate is the dimension:

sum(DailySettlement)

sum(if(SettlementDate = AsOfDate, ForecastSettlement, null()))

However, as I said this is highly inefficient (and dangerous - due to Cartesian products) and would probably fall over if you had too many rows.

Hope that all makes sense.

Regards,

Steve