topic Re: Aggregated data in the script in QlikView

Aggregated data in the script

helen_pip — Fri, 07 Jul 2017 12:59:33 GMT

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

Re: Aggregated data in the script

ahaahaaha — Fri, 07 Jul 2017 13:03:47 GMT

Hi Helen,

Is the value of all rows the same?

Re: Aggregated data in the script

helen_pip — Fri, 07 Jul 2017 13:04:43 GMT

Hello Andrey

Yes, the value in the rows is all the same

Kind Regards

Helen

Re: Aggregated data in the script

Kushal_Chawda — Fri, 07 Jul 2017 13:04:44 GMT

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');

Re: Aggregated data in the script

ahaahaaha — Fri, 07 Jul 2017 13:06:56 GMT

May be use DISTINCT at load time?

Re: Aggregated data in the script

Kushal_Chawda — Fri, 07 Jul 2017 13:07:27 GMT

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;

Re: Aggregated data in the script

ahaahaaha — Fri, 07 Jul 2017 13:08:21 GMT

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';

Re: Aggregated data in the script

helen_pip — Fri, 07 Jul 2017 13:27:36 GMT

Hello

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

Thanks

Helen

Re: Aggregated data in the script

Kushal_Chawda — Fri, 07 Jul 2017 13:34:20 GMT

Glad that it was helpful