topic combine aggregated fields in QlikView

combine aggregated fields

didierodayo — Tue, 29 Nov 2016 00:41:10 GMT

Hello,

I need some help with this scenario. I have the load script below.

Patient:

LOAD

Date,

[Patient Name],

[Room],

[Inssurance Limit],

Rate,

Max([Rate Value Avg]) as [Rate Value TOP],

Avg([Rate Value Avg])+Stdev([Rate Value Avg]) as [Rate Value + STD],

Max([Rate Value Avg],21) as [Rate Value 21],

Max([Rate Value Avg],14) as [Rate Value 14],

Max([Rate Value Avg],7) as [Rate Value 7]

Resident Patien_Temp Group By

Date,

[Patient Name],

[Room],

[Inssurance Limit],

Rate

STORE Patient Into Patient.qvd;
DROP TABLE Patien_Temp, Patient;

Now I would like to create a new field that will combine all the calculations below so that I can use that field in expressions to allow users to display the data based on the calculation they need.

[Rate Value TOP]

[Rate Value + STD]

[Rate Value 21]

[Rate Value 14]

[Rate Value 7]

Thanks

Re: combine aggregated fields

Tue, 29 Nov 2016 01:22:53 GMT

Didier,

Could you please elaborate your requirement.

-Ashok

Re: combine aggregated fields

didierodayo — Tue, 29 Nov 2016 01:43:08 GMT

Hi,

based n the table load below how could I achieve the following still in the load script?

- 36 months date forecast based on the current [Inssurance Rate] and Date I would like to know when the patient will hit the [Inssurance Limit].

Patient:

LOAD

Date,

[Patient Name],

[Room],

Rate

[Inssurance Rate],

[Inssurance Limit],

Max([Rate Value Avg]) as [Rate Value TOP],

Avg([Rate Value Avg])+Stdev([Rate Value Avg]) as [Rate Value + STD],

Max([Rate Value Avg],21) as [Rate Value 21],

Max([Rate Value Avg],14) as [Rate Value 14],

Max([Rate Value Avg],7) as [Rate Value 7]

Resident Patien_Temp Group By

Date,

[Patient Name],

[Room],

[Inssurance Rate],

[Inssurance Limit],

Rate;

STORE Patient Into Patient.qvd;
DROP TABLE Patien_Temp, Patient;

Re: combine aggregated fields

Tue, 29 Nov 2016 02:19:23 GMT

Didier,

Is your logic corrent? I haven't got your needs,you could think it more over, graph then better.

Re: combine aggregated fields

didierodayo — Tue, 29 Nov 2016 03:44:43 GMT

Hi,

I have refined the requirement. Thanks

Re: combine aggregated fields

Tue, 29 Nov 2016 03:56:45 GMT

Hi Didier,

so, what the purpuse will be like? As a field sellections? eg, while you choose [Rate Value TOP] of the new field (named NewFiled), any chart in the report will calc as method [Rate Value TOP]?

Re: combine aggregated fields

didierodayo — Tue, 29 Nov 2016 04:22:50 GMT

Yes I need it as a field selection .

Re: combine aggregated fields

Tue, 29 Nov 2016 04:55:55 GMT

is it like this?

CrossTable(NewField,Value,5)

LOAD Date,

[Patient Name],

[Room],

[Inssurance Limit],

Rate,

[Rate Value TOP],

[Rate Value + STD],

[Rate Value 21],

[Rate Value 14],

[Rate Value 7]

Resident Patient;

Re: combine aggregated fields

didierodayo — Tue, 29 Nov 2016 04:59:35 GMT

So NewField will be the new field name

Value ?

how is it adding the below to NewField?

[Rate Value TOP],

[Rate Value + STD],

[Rate Value 21],

[Rate Value 14],

[Rate Value 7]

Re: combine aggregated fields

Tue, 29 Nov 2016 05:08:05 GMT

You could try it,"NewField" will be the field name, "Value" will be the calclated value of one of the methods, "5" is the remaining fields number. For more details, see "Crosstable" function instructions.