Performance Issue by Using "&" in Set Analysis

[amit_saini]

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,

AS

[sunny_talwar]

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

[amit_saini]

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

Regards,

AS

[sunny_talwar]

I was only talking about these two fields

Station_Label & Line_Name

[amit_saini]

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,

AS

[sunny_talwar]

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?

[amit_saini]

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

[sunny_talwar · Accepted solution]

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;

[amit_saini]

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,

AS

[sunny_talwar]

Okay let us know if you see any performance improvement