Aggregated data in the script

[helen_pip]

Dear Qlikview User

I have created a table called Test, based on 2 other tables, so I get all the fields I want into 1 table. I then want to create a % figure

The script I have written is like this, and is being displayed at the lowest granularity

Test:

LOAD

Unique_ID,

FactDate,

data_source

Resident

main_data

WHERE FactDate >= MonthStart(Today()-15)

and FactDate <= MonthEnd(Today()-15);

outer join (Test)

LOAD

Unique_ID,

//Sum(Breach_4hr_Wait_Flag) as Breach_Sum,

Num(1-Sum(Breach_4hr_Wait_Flag)/Count(Unique_ID), '##.##%') as ED_%1

FROM

$(vQvdLayerPathName)ED_Data.qvd(qvd)

WHERE FactDate >= MonthStart(Today()-15)

and FactDate <= MonthEnd(Today()-15)

and ED_Planned_Attendance_Flag = '0'

and data_source = 'Main ED';

The data is returning the correct data value for field ED_%1 but I get 5829 rows of data.  I have tried grouping the data, but am struggling and I ideally only want 1 row of data at this stage

Any help would be greatly appreciated

Kind Regards

Helen

[ahaahaaha]

Hi Helen,

Is the value of all rows the same?

[helen_pip]

Hello Andrey

Yes, the value in the rows is all the same

Kind Regards

Helen

[Kushal_Chawda · Accepted solution]

why not just to store this value in variable instead of outer join

Value:

LOAD

Num(1-Sum(Breach_4hr_Wait_Flag)/Count(Unique_ID), '##.##%') as ED_%1

FROM

$(vQvdLayerPathName)ED_Data.qvd(qvd)

WHERE FactDate >= MonthStart(Today()-15)

and FactDate <= MonthEnd(Today()-15);

let vED_1 = peek ('ED_%1',0,'Value');

[ahaahaaha]

May be use DISTINCT at load time?

[Kushal_Chawda]

missing group by?

LOAD

Unique_ID,

//Sum(Breach_4hr_Wait_Flag) as Breach_Sum,

Num(1-Sum(Breach_4hr_Wait_Flag)/Count(Unique_ID), '##.##%') as ED_%1

FROM

$(vQvdLayerPathName)ED_Data.qvd(qvd)

WHERE FactDate >= MonthStart(Today()-15)

and FactDate <= MonthEnd(Today()-15)

and ED_Planned_Attendance_Flag = '0'

and data_source = 'Main ED'

group by Unique_ID;

[ahaahaaha]

Test:

LOAD DISTINCT

Unique_ID,

FactDate,

data_source

Resident

main_data

WHERE FactDate >= MonthStart(Today()-15)

and FactDate <= MonthEnd(Today()-15);

outer join (Test)

LOAD DISTINCT

Unique_ID,

//Sum(Breach_4hr_Wait_Flag) as Breach_Sum,

Num(1-Sum(Breach_4hr_Wait_Flag)/Count(Unique_ID), '##.##%') as ED_%1

FROM

$(vQvdLayerPathName)ED_Data.qvd(qvd)

WHERE FactDate >= MonthStart(Today()-15)

and FactDate <= MonthEnd(Today()-15)

and ED_Planned_Attendance_Flag = '0'

and data_source = 'Main ED';

[helen_pip]

Hello

Thanks for your suggestion, with a couple of tweaks I can get this working for me

Thanks

Helen

[Kushal_Chawda]

Glad that it was helpful