topic Re: Linking calculated fields in QlikView

Linking calculated fields

Wed, 19 Aug 2015 17:38:04 GMT

Hi,

I have a calculated field which I am trying to join with a Date field so that I might see it by date. Despite, both the fields used in the calculation being linked to a date field, I can't find a way to get the calculated field by date. The script I'm using is below:

CalculatedFields:
LOAD
Date,
SUM(Alpha* Beta) AS Charlie
RESIDENT SecurityData
group by
Date,
ProductID,

Alpha,

Beta

Charlie,

Delta

etc;

Any ideas how I can get the two to link?

Re: Linking calculated fields

maxgro — Wed, 19 Aug 2015 17:46:11 GMT

I think this: you only have the Date field as non aggregated field, so you need to group by Date

CalculatedFields:
LOAD
Date,
SUM(Alpha* Beta) AS Charlie
RESIDENT SecurityData
group by
Date;

Re: Linking calculated fields

swuehl — Wed, 19 Aug 2015 17:47:53 GMT

Could you elaborate with some sample data lines of SecurityData (could be mock up data, but the structure should be comparable)?

Re: Linking calculated fields

Wed, 19 Aug 2015 17:48:00 GMT

Worked perfectly, thanks so much!

Re: Linking calculated fields

Wed, 19 Aug 2015 17:52:08 GMT

As a follow up question, how can I group by fields in another resident table?

Re: Linking calculated fields

maxgro — Wed, 19 Aug 2015 18:15:36 GMT

in this script you're already group by from a table (SecurityData)

and the result is a new table (CalculatedFields)

CalculatedFields:
LOAD
Date,
SUM(Alpha* Beta) AS Charlie
RESIDENT SecurityData
group by Date;

maybe I don't understand your question

Re: Linking calculated fields

Wed, 19 Aug 2015 18:18:14 GMT

Apologies, I have loaded in two tables SecurityData and a second called SecurityCharacteristics which contains a field, for instance, of Region.

I want to be able to sort my calculated field by Region.

Re: Linking calculated fields

maxgro — Wed, 19 Aug 2015 18:41:29 GMT

in Qlik you can sort (or group by) fields in one table

if you need to sort (or group by) fields from 2 tables, first you have to join the tables in a a new one

then do a resident load (with sort or group by) from the new one