Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Improvement of scripting - please help!

Hi all,

I'm currently using the script below, essentially it loads the same data set but refines it by a few criteria each time, it then converts it into a format to fit with a larger system. I want to simplify it so that I don't have to have a complete select statement for each variable. I have tried performing a standard Load statement with IF statements and Counts, but cannot get it to function.

In the end I really want to end up with a table of data which is merely Period, Fiscal Year, Structure Key and Overall Count or value. My current script produces this but I was hoping that you guys might be able to suggest a more efficient way of making it work?

Thanks again

Ben

////================================================================================

// COMPLAINTS DATA

////================================================================================

ODBC CONNECT TO Standard_Apps_Data;

Complaints_Data:

////================================================================================

// Invoice Issues

////================================================================================

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P1Invoice

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P1 - Critical'

and Complaint_Category='Service'

and Complaint_Type='Documentation'

and Complaint_Component='Invoice'

group by Period_Reported

;

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P2Invoice

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P2 - Major'

and Complaint_Category='Service'

and Complaint_Type='Documentation'

and Complaint_Component='Invoice'

group by Period_Reported

;

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P3Invoice

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P3 - Minor'

and Complaint_Category='Service'

and Complaint_Type='Documentation'

and Complaint_Component='Invoice'

group by Period_Reported

;

////=================================================================================

// Quality Issues

////=================================================================================

//==============SUGAR QUALITY========================================================

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P1QualitySugar

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P1 - Critical'

and Complaint_Category='Product'

and Complaint_Type='Quality'

and Complaint_Component='Sugar'

group by Period_Reported

;

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P2QualitySugar

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P2 - Major'

and Complaint_Category='Product'

and Complaint_Type='Quality'

and Complaint_Component='Sugar'

group by Period_Reported

;

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P3QualitySugar

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P3 - Minor'

and Complaint_Category='Product'

and Complaint_Type='Quality'

and Complaint_Component='Sugar'

group by Period_Reported

;

//==============LIQUID QUALITY========================================================

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P1QualityLiquid

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P1 - Critical'

and Complaint_Category='Product'

and Complaint_Type='Quality'

and Complaint_Component='Liquid'

group by Period_Reported

;

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P2QualityLiquid

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P2 - Major'

and Complaint_Category='Product'

and Complaint_Type='Quality'

and Complaint_Component='Liquid'

group by Period_Reported

;

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P3QualityLiquid

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P3 - Minor'

and Complaint_Category='Product'

and Complaint_Type='Quality'

and Complaint_Component='Liquid'

group by Period_Reported

;

//==============PACKAGING QUALITY========================================================

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P1QualityPackaging

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P1 - Critical'

and Complaint_Category='Product'

and Complaint_Type='Quality'

and Complaint_Component='Packaging'

group by Period_Reported

;

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P2QualityPackaging

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P2 - Major'

and Complaint_Category='Product'

and Complaint_Type='Quality'

and Complaint_Component='Packaging'

group by Period_Reported

;

Join (Complaints_Data)

Select

Left(Period_Reported,2) as Period,

Right(Period_Reported,4) as Fiscal_Year,

Count(*) as P3QualityPackaging

FROM "Complaints_Data_Prototype".dbo.T600

Where Complaint_Priority='P3 - Minor'

and Complaint_Category='Product'

and Complaint_Type='Quality'

and Complaint_Component='Packaging'

group by Period_Reported

;

////=============================================================================

// COMPLAINTS SET

////============================================================================

Complaints:

Crosstable (ComplaintsTempDesc, Value,2)

Load

Period,

Fiscal_Year,

P1Invoice,

P2Invoice,

P3Invoice,

P1QualitySugar,

P2QualitySugar,

P3QualitySugar,

P1QualityLiquid,

P2QualityLiquid,

P3QualityLiquid,

P1QualityPackaging,

P2QualityPackaging,

P3QualityPackaging

Resident Complaints_Data;

Left Join (Complaints)

Load * Inline [ComplaintsTempDesc, StructureKey%

;

Drop Field ComplaintsTempDesc;

Drop Table Complaints_Data;



Community Browser