Refer to a value from another Straight Table

[#1]

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.

PC

[#2]

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

[Jason_Michaelides]

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

[#4]

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.

[#5]

Hi Jason,

No, the tables are not linked with each other.

Cheers,

PC

[#6]

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.

[stevedark]

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