topic Re: Performance Issue by Using "&" in Set Analysis in QlikView

Performance Issue by Using "&" in Set Analysis

amit_saini — Mon, 08 Oct 2018 13:48:47 GMT

Hi Folks,

I'm using below expression in a bar chart:

count(DISTINCT {<Month=,Week=,Day=,Type_Name=,Why_Name=,Reason_Name=,Manning_Compensation=>}Station_Label & Line_Name)

It is business requirement to take distinct count of Station_Label & Line_Name together , which is impacting chart performance.

Is there any way to write this logic without "&" ???? As it is killing chart performance.

Regards,

Re: Performance Issue by Using "&" in Set Analysis

sunny_talwar — Mon, 08 Oct 2018 14:01:28 GMT

Can the fields be combined in the script? If they are coming from different tables, then it explains the performance issue... in which case you can try combining them into a single table to see if that improve performance

Re: Performance Issue by Using "&" in Set Analysis

amit_saini — Mon, 08 Oct 2018 14:15:41 GMT

Not all the fields in Set Analysis belongs to one table, let me try as suggested!

Regards,

Re: Performance Issue by Using "&" in Set Analysis

sunny_talwar — Mon, 08 Oct 2018 14:17:35 GMT

I was only talking about these two fields

Station_Label & Line_Name

Re: Performance Issue by Using "&" in Set Analysis

amit_saini — Mon, 08 Oct 2018 14:24:17 GMT

Data:

LOAD

Station_Id ,

ApplyMap('Mapp_Stat',Station_Id,'NA') as Station_Label,

ScheduledMinutes

FROM

$(vDataPath)Planned_Downtime_Stations.qvd

(qvd);

Left Join (Data)

LOAD Id,

Line_Id,

ApplyMap('Mapp_Line',Line_Id,'NA') as Line_Name

FROM

$(vDataPath)Planned_Downtime.qvd

So, it is coming from one table (Left Join Data).

Thanks,

Re: Performance Issue by Using "&" in Set Analysis

sunny_talwar — Mon, 08 Oct 2018 14:28:41 GMT

What are these two tables joined on? I see Station_Id in the first table, but not in the second one. and I see Id and Line_Id in the second one, but not in the first one. Is there a Cartesian Join by mistake here?

Re: Performance Issue by Using "&" in Set Analysis

amit_saini — Mon, 08 Oct 2018 14:34:23 GMT

Sorry , they are connected with a common field "Id", like below:

Data:

LOAD

num(Stamp_Date) as Date.tmp2,

timestamp(Stamp_Date) as Date_Org,

Id,

Station_Id ,

ApplyMap('Mapp_Stat',Station_Id,'NA') as Station_Label,

ApplyMap('Mapp_Line_Id',Id,'NA') as Line_Id,

ScheduledMinutes

FROM

$(vDataPath)Planned_Downtime_Stations.qvd

(qvd);

Left Join (Data)

LOAD Id,

Line_Id,

ApplyMap('Mapp_Line',Line_Id,'NA') as Line_Name,

if(Compensation='0','NO','Yes') as Manning_Compensation,

Comments as Comment_PD

FROM

$(vDataPath)Planned_Downtime.qvd

(qvd);

Thanks,
AS

Re: Performance Issue by Using "&" in Set Analysis

sunny_talwar — Mon, 08 Oct 2018 14:38:14 GMT

May be try this

Data:

LOAD

num(Stamp_Date) as Date.tmp2,

timestamp(Stamp_Date) as Date_Org,

Id,

Station_Id ,

ApplyMap('Mapp_Stat',Station_Id,'NA') as Station_Label,

ApplyMap('Mapp_Line_Id',Id,'NA') as Line_Id,

ScheduledMinutes

FROM

$(vDataPath)Planned_Downtime_Stations.qvd

(qvd);

Left Join (Data)

LOAD Id,

Line_Id,

ApplyMap('Mapp_Line',Line_Id,'NA') as Line_Name,

if(Compensation='0','NO','Yes') as Manning_Compensation,

Comments as Comment_PD

FROM $(vDataPath)Planned_Downtime.qvd (qvd);

New_Data:

LOAD *,

Station_Label & Line_Name as Station_Label_And_Line_Name_Combined

Resident Data;

DROP Table Data;

Re: Performance Issue by Using "&" in Set Analysis

amit_saini — Mon, 08 Oct 2018 14:41:25 GMT

Yes, trying as below:

Data_NEW:

NoConcatenate

LOAD

Line_Name&'_'&Station_Label as %KEY_Line_Station

Resident Data;

Drop Table CAQ;

RENAME Table Data_NEW to Data;

Regards,

Re: Performance Issue by Using "&" in Set Analysis

sunny_talwar — Mon, 08 Oct 2018 14:45:16 GMT

Okay let us know if you see any performance improvement

Re: Performance Issue by Using "&" in Set Analysis

marcus_sommer — Mon, 08 Oct 2018 15:03:14 GMT

In addition to the approach of bringing both fields within one table you could improve the performance if you used numerical ID's instead of string-values for your counting - maybe creating them additionally as an autonumber-field or you used dual() to assign a numerical value to the string.

After this you could use these ID's or num(DualValue) and it should be already faster. More speed might be possible if you then replaced the & with a + like:

count(distinct Field1 * 10000 + Field2)

The 10000 is just an example that you need any measure to assure that a summing of both fields will always return a unique value and if any of both fieldvalues is NULL the + operator makes it complete NULL (if you also want to count these values just take a rangesum() instead of the +).

- Marcus

Re: Performance Issue by Using "&" in Set Analysis

amit_saini — Tue, 09 Oct 2018 09:49:42 GMT

I tried above code and performance is far much better.

Thanks,