combine aggregated fields

[didierodayo]

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

[#2]

Didier,

Could you please elaborate your requirement.

-Ashok

[didierodayo]

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;

[#4]

Didier,

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

[didierodayo]

Hi,

I have refined the requirement. Thanks

[#6]

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]?

[didierodayo]

Yes I need it as a field selection .

[#8 · Accepted solution]

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;

[didierodayo]

So NewField will be the new field name

Value ?

5?

how is it adding the below to NewField?

     [Rate Value TOP],

     [Rate Value + STD],

     [Rate Value 21],

     [Rate Value 14],

     [Rate Value 7]

[#10]

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.