topic Re: Concat Function To Get Top Performers in QlikView

Concat Function To Get Top Performers

kaygee28 — Tue, 29 Mar 2016 10:00:31 GMT

Good Day, I've got a problem with regards to using the concat function in order to achieve what I want at the moment.

I have a table in which I used the applymap function and I created fields within those loading statements that I want to utilize in my concat statement, If you have a look at the code below I created the [Cars Bought] field from the AssetDesc field which is in the applymap function, now what I want to do is I want the [Job Titles] field and the top 3 highest sales [Cars Bought] within a table box and the concat statement will separate the [Cars Bought] with commas.

Vehicle_Map:

Mapping LOAD DealRef,

AssetDesc

FROM

[Demographica Portfolio Info V3 Data Models.xlsx]

(ooxml, embedded labels, table is [Demographica Portfolio Info V3 ]);

Demographics:

LOAD *,

if(wildmatch(AssetDesc,'*BMW 1*'), '1 Series Model',AssetDesc) AS [Cars Bought];

App_Status:

LOAD APPStatus,

Contract_Ref_no,

Applymap('Vehicle_Map',Contract_Ref_no,'NULL') AS AssetDesc,

Start_Day,

If((EDUCATION_EXPENSE_AMT)>0,'Educational Expenditure','No Educational Expenditure') AS [Parents/NotParents],

if(wildmatch(job_title,'*Advocate*'), 'Legal Practitioner') AS [Job Titles]

FROM

[BMW Consolidated SF Data 10032016.xlsx]

(ooxml, embedded labels, table is [BMW (45)]);

I want to insert the below code within the script in order to achieve this, however it keeps coming back with errors hence I'd love to get as much help with this as possible:

Top_Performers:

LOAD

CONCAT(IF(aggr(Rank(count([Cars Bought])),[Job Titles])<=3,[Job Titles]),',') AS [Top Performers];

This I will then utilize in my table box to get the three top cars that are being bought for each job title, I however realize that the two fields are not from the same table that was loaded.

Thanks in advance, I hope I make sense though.

Re: Concat Function To Get Top Performers

kaygee28 — Tue, 29 Mar 2016 13:49:29 GMT

Actually I want the top 3 max vehicles sold for each [Job Titles] and not the top 3, hence the Max function needs to be inserted.

Re: Concat Function To Get Top Performers

sunny_talwar — Tue, 29 Mar 2016 13:55:40 GMT

Is it possible to share a sample application to look at the issue?

Re: Concat Function To Get Top Performers

sunny_talwar — Tue, 29 Mar 2016 13:56:37 GMT

or share the script that you are using?

Re: Concat Function To Get Top Performers

kaygee28 — Tue, 29 Mar 2016 14:01:01 GMT

Hi Sunny T I am actually on Personal Edition so that is virtually impossible to do.

I just want the top 3 vehicles sold for each job title.

i.e I want a table like this:

Title Top Performers

doctor bmw 3series, bmw 4 series, bmw 5 series

however the Title and the vehicles which are being displayed are derived fields which I created in the load statements illustrated in the original post.

Re: Concat Function To Get Top Performers

sunny_talwar — Tue, 29 Mar 2016 16:18:36 GMT

You are using personal edition, but people here are not and hence will be able to open your application. If you can share your application, we can provide a solution by copy pasting the script directly here so that you don't have to open the a modified application on your computer

Re: Concat Function To Get Top Performers

Tue, 29 Mar 2016 16:34:46 GMT

Hi Kagisko,

So, what I understood is that you have the below data and you need the output like below.

input:

Title Cars

------- -------

doctor1 bmw1

doctor1 bmw2

doctor1 bmw3

doctor1 bmw4

doctor2 bmw1

doctor2 bmw2

output:

Title Cars

doctor1 bmw1,bmw2,bmw3

doctor2 bmw1, bmw2

Thanks,

Sreeman

Re: Concat Function To Get Top Performers

kaygee28 — Tue, 29 Mar 2016 19:24:24 GMT

Hi sreeman.sap,

Sorry for the late reply that's exactly what I want to achieve, however the two fields are derived fields which I created in my script, thats [Job Title] and [Cars Bought].

i.e they were created using if statements.

Re: Concat Function To Get Top Performers

kaygee28 — Tue, 29 Mar 2016 19:35:42 GMT

SET ThousandSep=' ';

SET DecimalSep='.';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='R # ##0,00;R-# ##0,00';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='YYYY/MM/DD';

SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Vehicle_Map:

Mapping LOAD DealRef,

AssetDesc

FROM

[Demographica Portfolio Info V3 Data Models.xlsx]

(ooxml, embedded labels, table is [Demographica Portfolio Info V3 ]);

Demographics:

LOAD *,

if(wildmatch(AssetDesc,'*BMW 1*'), '1 Series Model',

if(wildmatch(AssetDesc,'*BMW 2*'), '2 Series Model',

if(wildmatch(AssetDesc,'*BMW 3*'), '3 Series Model',

if(wildmatch(AssetDesc,'*BMW 4*'), '4 Series Model',

if(wildmatch(AssetDesc,'*BMW 5*'), '5 Series Model',

if(wildmatch(AssetDesc,'*BMW 6*'), '6 Series Model',

if(wildmatch(AssetDesc,'*BMW 7*'), '7 Series Model',

if(wildmatch(AssetDesc,'*BMW X1*'), 'X1 Model',

if(wildmatch(AssetDesc,'*BMW X3*'), 'X3 Model',

if(wildmatch(AssetDesc,'*BMW X5*'), 'X5 Model',

if(wildmatch(AssetDesc,'*BMW X6*'), 'X6 Model',

if(wildmatch(AssetDesc,'*BMW Z3*'), 'Z3 Model',

if(wildmatch(AssetDesc,'*BMW Z4*'), 'Z4 Model',

if(wildmatch(AssetDesc,'*BMW M3*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M5*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M1*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M4*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M6*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW R*'), 'BMW R Model',

if(wildmatch(AssetDesc,'*Mini*'), 'MINI Model',AssetDesc))))))))))))))))))))

AS [Cars Bought],

if(wildmatch(AssetDesc,'*BMW*'), 'BMW Group',

if(wildmatch(AssetDesc,'*Mini*'), 'BMW Group','Other Brands')) AS [BMW/Other Brands];

App_Status:

LOAD APPStatus,

Contract_Ref_no,

Applymap('Vehicle_Map',Contract_Ref_no,'NULL') AS AssetDesc,

Start_Day,

Start_Month,

[Start Year],

Status_cd,

title_cd,

Customer_name,

race_type_cd,

Gender,

Language_cd,

Employ_status_cd,

No_of_years,

No_of_months,

job_title,

employer_name,

GROSS_REMUNERATION_AMT,

MONTHLY_COMM_AMT,

CAR_ALLOWANCE_AMT,

NET_PAY_AMT,

ADDL_INCOME_SOURCE,

BOND_RENT_PAY_AMT,

VEHICLE_INSTALL_AMT,

CREDIT_CARD_REPAY_AMT,

CLOTHING_EXPENSE_AMT,

INS_REPAYMENT_AMT,

TRANSPORT_COST_AMT,

EDUCATION_EXPENSE_AMT,

ADD_COST_AMT,

HOUSEHOLD_EXP_AMT,

RWE_AMT,

PL_REPAYMENT_AMT,

FURNITURE_AMT,

OVERDRAFT_REPAY_AMT,

PHONE_REPAYMENT_AMT,

FOOD_ENTERTAINMENT_AMT,

MAINTENANCE_AMT,

OTHER_EXPENSE_AMT,

TOTAL_MONTHLY_EXP_AMT,

DISPOSABLE_INCOME_AMT,

If((EDUCATION_EXPENSE_AMT)>0,'Educational Expenditure','No Educational Expenditure') AS [Parents/NotParents],

If((Contract_Ref_no) and (Customer_name)>1,'Repurchases','First Time Buyers') AS [Repurchases],

if(wildmatch(job_title,'*Advocate*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Doctor*'), 'Medical Doctor',

if(wildmatch(job_title,'*Medical Doctor*'), 'Medical Doctor',

if(wildmatch(job_title,'*Lawyer*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Developer*'), 'IT Practitioner',

if(wildmatch(job_title,'*Programmer*'), 'IT Practitioner',

if(wildmatch(job_title,'*Accountant*'), 'Accountant',

if(wildmatch(job_title,'*Engineer*'), 'Engineer',

if(wildmatch(job_title,'*Owner*'), 'Self Employed',

if(wildmatch(job_title,'*Medical Practitioner*'), 'Medical Doctor',

if(wildmatch(job_title,'*Judge*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Legal Executive*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Family Advocate*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Nurse*'), 'Nurse',

if(wildmatch(job_title,'*Teacher*'), 'Teacher')))))))))))))))

AS [Job Titles]

FROM

[BMW Consolidated SF Data 10032016.xlsx]

(ooxml, embedded labels, table is [BMW (45)]);

//I am not sure this is what you mean

Re: Concat Function To Get Top Performers

Wed, 30 Mar 2016 09:10:25 GMT

Hi Kagiso,

Can you please see the below script based on it you will get the data. But here you need an order by clause based on what columns you need to get the top3 values per [Job Title].

input:

load * inline [

Title,Cars

doctor1,bmw1

doctor1,bmw2

doctor1,bmw3

doctor1,bmw4

doctor2,bmw1

doctor2,bmw2

];

NoConcatenate

output:

load Title,

/* if(isnull(Previous(Title))<>0,Cars,if(Title<>Previous(Title),Cars,concat(peek("Cars"),concat(',',Cars)))) as cars*/

if(Title<>Previous(Title),Cars,peek("cars")&','& Cars) as cars,

if(Title<>Previous(Title),1,peek("rank")+1) as rank

Resident input;

NoConcatenate

finaloutput:

load Title as tt,

cars as cc

Resident output where rank<4;

Thanks,
Sreeman.

Re: Concat Function To Get Top Performers

kaygee28 — Wed, 30 Mar 2016 09:17:11 GMT

Hi Sreeman, I'll have a look try understand your script then implement in my app and see if it works I'll revert back.

Thanks a lot for your help.

Re: Concat Function To Get Top Performers

Kushal_Chawda — Wed, 30 Mar 2016 10:26:41 GMT

Vehicle_Map:

Mapping LOAD DealRef,

AssetDesc

FROM

[Demographica Portfolio Info V3 Data Models.xlsx]

(ooxml, embedded labels, table is [Demographica Portfolio Info V3 ]);

Demographics:

LOAD *,

if(wildmatch(AssetDesc,'*BMW 1*'), '1 Series Model',

if(wildmatch(AssetDesc,'*BMW 2*'), '2 Series Model',

if(wildmatch(AssetDesc,'*BMW 3*'), '3 Series Model',

if(wildmatch(AssetDesc,'*BMW 4*'), '4 Series Model',

if(wildmatch(AssetDesc,'*BMW 5*'), '5 Series Model',

if(wildmatch(AssetDesc,'*BMW 6*'), '6 Series Model',

if(wildmatch(AssetDesc,'*BMW 7*'), '7 Series Model',

if(wildmatch(AssetDesc,'*BMW X1*'), 'X1 Model',

if(wildmatch(AssetDesc,'*BMW X3*'), 'X3 Model',

if(wildmatch(AssetDesc,'*BMW X5*'), 'X5 Model',

if(wildmatch(AssetDesc,'*BMW X6*'), 'X6 Model',

if(wildmatch(AssetDesc,'*BMW Z3*'), 'Z3 Model',

if(wildmatch(AssetDesc,'*BMW Z4*'), 'Z4 Model',

if(wildmatch(AssetDesc,'*BMW M3*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M5*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M1*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M4*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M6*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW R*'), 'BMW R Model',

if(wildmatch(AssetDesc,'*Mini*'), 'MINI Model',AssetDesc))))))))))))))))))))

AS [Cars Bought],

if(wildmatch(AssetDesc,'*BMW*'), 'BMW Group',

if(wildmatch(AssetDesc,'*Mini*'), 'BMW Group','Other Brands')) AS [BMW/Other Brands];

LOAD APPStatus,

Contract_Ref_no,

Applymap('Vehicle_Map',Contract_Ref_no,'NULL') AS AssetDesc,

Start_Day,

Start_Month,

[Start Year],

Status_cd,

title_cd,

Customer_name,

race_type_cd,

Gender,

Language_cd,

Employ_status_cd,

No_of_years,

No_of_months,

job_title,

employer_name,

GROSS_REMUNERATION_AMT,

MONTHLY_COMM_AMT,

CAR_ALLOWANCE_AMT,

NET_PAY_AMT,

ADDL_INCOME_SOURCE,

BOND_RENT_PAY_AMT,

VEHICLE_INSTALL_AMT,

CREDIT_CARD_REPAY_AMT,

CLOTHING_EXPENSE_AMT,

INS_REPAYMENT_AMT,

TRANSPORT_COST_AMT,

EDUCATION_EXPENSE_AMT,

ADD_COST_AMT,

HOUSEHOLD_EXP_AMT,

RWE_AMT,

PL_REPAYMENT_AMT,

FURNITURE_AMT,

OVERDRAFT_REPAY_AMT,

PHONE_REPAYMENT_AMT,

FOOD_ENTERTAINMENT_AMT,

MAINTENANCE_AMT,

OTHER_EXPENSE_AMT,

TOTAL_MONTHLY_EXP_AMT,

DISPOSABLE_INCOME_AMT,

If((EDUCATION_EXPENSE_AMT)>0,'Educational Expenditure','No Educational Expenditure') AS [Parents/NotParents],

If((Contract_Ref_no) and (Customer_name)>1,'Repurchases','First Time Buyers') AS [Repurchases],

if(wildmatch(job_title,'*Advocate*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Doctor*'), 'Medical Doctor',

if(wildmatch(job_title,'*Medical Doctor*'), 'Medical Doctor',

if(wildmatch(job_title,'*Lawyer*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Developer*'), 'IT Practitioner',

if(wildmatch(job_title,'*Programmer*'), 'IT Practitioner',

if(wildmatch(job_title,'*Accountant*'), 'Accountant',

if(wildmatch(job_title,'*Engineer*'), 'Engineer',

if(wildmatch(job_title,'*Owner*'), 'Self Employed',

if(wildmatch(job_title,'*Medical Practitioner*'), 'Medical Doctor',

if(wildmatch(job_title,'*Judge*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Legal Executive*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Family Advocate*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Nurse*'), 'Nurse',

if(wildmatch(job_title,'*Teacher*'), 'Teacher')))))))))))))))

AS [Job Titles]

FROM

[BMW Consolidated SF Data 10032016.xlsx]

(ooxml, embedded labels, table is [BMW (45)]);

JobTitle:

LOAD [Job Titles],

[Cars Bought] as CarBoughtNew

Count([Cars Bought]) as CountOfCars

resident Demographics

Group By [Job Titles],[Cars Bought];

New:

LOAD [Job Titles],

CarBoughtNew,

Autonumber(CountOfCars,[Job Titles]) as Rank

Resident JobTitle

order by [Job Titles],CarBoughtNew desc;

Drop Table JobTitle;

Top3:

noconcatenate

LOAD *

Resident New

where Rank <=3;

Drop Table New;

Final:

LOAD [Job Titles],

Concat(Distinct CarBoughtNew,',') as Top3Cars

Resident Top3

Group by [Job Titles];

Drop Table Top3;

Now Create the Table box and Add two fields [Job Titles] and Top3Cars

Re: Concat Function To Get Top Performers

kaygee28 — Wed, 30 Mar 2016 12:27:46 GMT

Hi Sreeman, I just tried the script it gives me the Job Titles with a long list of all the cars they have bought and not the top 3 vehichles in each job title.

See image attached, I even ordered by the job title as well as the cars being bough please see script attached.

NoConcatenate

Top_Rankings:

load [Job Titles],

if([Job Titles]<>Previous([Job Titles]),[Cars Bought],peek("[Cars Bought]")&','& [Cars Bought]) as Cars,

if([Job Titles]<>Previous([Job Titles]),1,peek("rank")+1) as rank

Resident App_Status;

NoConcatenate

Top_Rankings_2:

load [Job Titles] as [Top 5 Titles],

Cars as [Top Cars]

Resident Top_Rankings where rank<4 order by [Job Titles],Cars;

I want the top three cars for each job title to be stacked on each other

Re: Concat Function To Get Top Performers

kaygee28 — Wed, 30 Mar 2016 12:51:25 GMT

You've been really helpful guys but your script still returns all the cars Kush141087, see image below.

However the good thing is you dropped the tables hence it never brought back more than 600000 records, its only like less than 5000 which shows that its working.

So what I think its doing is that its numbering each and every car and only returns the cars which have been numbered 1 to 3.

Re: Concat Function To Get Top Performers

Wed, 30 Mar 2016 12:55:53 GMT

Hi Kagiso,

You have to keep the order by clause in the Top_Rankings section. Can you please try by placing the Order by clause

Thanks,
Sreeman

Re: Concat Function To Get Top Performers

kaygee28 — Wed, 30 Mar 2016 18:58:43 GMT

Hi Sreeman, the graph still doesn't change hence I say I think a number is placed on each vehicle and the ranks are done according to the vehicle number and not according to the number of cars sold.

Thanks anyway.

Re: Concat Function To Get Top Performers

Kushal_Chawda — Thu, 31 Mar 2016 10:42:41 GMT

But I can see only 3 Cars. n Top 3.

Can add Job Title and Top 3 field in table box and send the screenshot?

or else you can share some sample to work on same

Re: Concat Function To Get Top Performers

kaygee28 — Thu, 31 Mar 2016 11:28:46 GMT

Hi Kush141087, oh i figured out that cars bought has non bmw vehicles which I might make the list endless hence I developed a new field that that only looked at BMW vehicles, hence now it only displays BMW vehicles but I dont think it's accurate. Here is the code below, I think ther is something really small I am missing,:

Unfortunately i can't attach the app so that you could see what I am talking about.

SET ThousandSep=' ';

SET DecimalSep='.';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='R # ##0,00;R-# ##0,00';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='YYYY/MM/DD';

SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Vehicle_Map:

Mapping LOAD DealRef,

AssetDesc

FROM

[Demographica Portfolio Info V3 Data Models.xlsx]

(ooxml, embedded labels, table is [Demographica Portfolio Info V3 ]);

Demographics:

LOAD *,

if(wildmatch(AssetDesc,'*BMW 1*'), '1 Series Model',

if(wildmatch(AssetDesc,'*BMW 2*'), '2 Series Model',

if(wildmatch(AssetDesc,'*BMW 3*'), '3 Series Model',

if(wildmatch(AssetDesc,'*BMW 4*'), '4 Series Model',

if(wildmatch(AssetDesc,'*BMW 5*'), '5 Series Model',

if(wildmatch(AssetDesc,'*BMW 6*'), '6 Series Model',

if(wildmatch(AssetDesc,'*BMW 7*'), '7 Series Model',

if(wildmatch(AssetDesc,'*BMW X1*'), 'X1 Model',

if(wildmatch(AssetDesc,'*BMW X3*'), 'X3 Model',

if(wildmatch(AssetDesc,'*BMW X5*'), 'X5 Model',

if(wildmatch(AssetDesc,'*BMW X6*'), 'X6 Model',

if(wildmatch(AssetDesc,'*BMW Z3*'), 'Z3 Model',

if(wildmatch(AssetDesc,'*BMW Z4*'), 'Z4 Model',

if(wildmatch(AssetDesc,'*BMW M3*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M5*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M1*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M4*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M6*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW R*'), 'BMW R Model',

if(wildmatch(AssetDesc,'*Mini*'), 'MINI Model',AssetDesc))))))))))))))))))))

AS [Cars Bought],

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//This [Cars Bought1] field was developed in order to calculate without other vehicles being there,

if(wildmatch(AssetDesc,'*BMW 1*'), '1 Series Model',

if(wildmatch(AssetDesc,'*BMW 2*'), '2 Series Model',

if(wildmatch(AssetDesc,'*BMW 3*'), '3 Series Model',

if(wildmatch(AssetDesc,'*BMW 4*'), '4 Series Model',

if(wildmatch(AssetDesc,'*BMW 5*'), '5 Series Model',

if(wildmatch(AssetDesc,'*BMW 6*'), '6 Series Model',

if(wildmatch(AssetDesc,'*BMW 7*'), '7 Series Model',

if(wildmatch(AssetDesc,'*BMW X1*'), 'X1 Model',

if(wildmatch(AssetDesc,'*BMW X3*'), 'X3 Model',

if(wildmatch(AssetDesc,'*BMW X5*'), 'X5 Model',

if(wildmatch(AssetDesc,'*BMW X6*'), 'X6 Model',

if(wildmatch(AssetDesc,'*BMW Z3*'), 'Z3 Model',

if(wildmatch(AssetDesc,'*BMW Z4*'), 'Z4 Model',

if(wildmatch(AssetDesc,'*BMW M3*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M5*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M1*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M4*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW M6*'), 'M Models',

if(wildmatch(AssetDesc,'*BMW R*'), 'BMW R Model',

if(wildmatch(AssetDesc,'*Mini*'), 'MINI Model','Non BMW'))))))))))))))))))))

AS [Cars Bought1],

if(wildmatch(AssetDesc,'*BMW*'), 'BMW Group',

if(wildmatch(AssetDesc,'*Mini*'), 'BMW Group','Other Brands')) AS [BMW/Other Brands];

App_Status:

LOAD APPStatus,

Contract_Ref_no,

Applymap('Vehicle_Map',Contract_Ref_no,'NULL') AS AssetDesc,

Start_Day,

Start_Month,

[Start Year],

Status_cd,

title_cd,

Customer_name,

race_type_cd,

Gender,

Language_cd,

Employ_status_cd,

No_of_years,

No_of_months,

job_title,

employer_name,

GROSS_REMUNERATION_AMT,

MONTHLY_COMM_AMT,

CAR_ALLOWANCE_AMT,

NET_PAY_AMT,

ADDL_INCOME_SOURCE,

BOND_RENT_PAY_AMT,

VEHICLE_INSTALL_AMT,

CREDIT_CARD_REPAY_AMT,

CLOTHING_EXPENSE_AMT,

INS_REPAYMENT_AMT,

TRANSPORT_COST_AMT,

EDUCATION_EXPENSE_AMT,

ADD_COST_AMT,

HOUSEHOLD_EXP_AMT,

RWE_AMT,

PL_REPAYMENT_AMT,

FURNITURE_AMT,

OVERDRAFT_REPAY_AMT,

PHONE_REPAYMENT_AMT,

FOOD_ENTERTAINMENT_AMT,

MAINTENANCE_AMT,

OTHER_EXPENSE_AMT,

TOTAL_MONTHLY_EXP_AMT,

DISPOSABLE_INCOME_AMT,

If((EDUCATION_EXPENSE_AMT)>0,'Educational Expenditure','No Educational Expenditure') AS [Parents/NotParents],

If((DISPOSABLE_INCOME_AMT)>10000,'Dispo. Income >R10000','Lower than R10000') AS [Disposable Income],

If((MAINTENANCE_AMT)>0,'Maintenance Expenses','No Maintenance Expenditure') AS [Maintenance],

If(( CLOTHING_EXPENSE_AMT)>0 and (HOUSEHOLD_EXP_AMT)>0 and (FURNITURE_AMT)>0 and (FOOD_ENTERTAINMENT_AMT)>0,'Expenses Excluding Cellphone Bill',

If(( CLOTHING_EXPENSE_AMT)>0 and (HOUSEHOLD_EXP_AMT)>0 and (FOOD_ENTERTAINMENT_AMT)>0 and (PHONE_REPAYMENT_AMT)>0,'Expenses Inluding Cellphone Bill',

If(( CLOTHING_EXPENSE_AMT)>0 and (HOUSEHOLD_EXP_AMT)>0 and (CREDIT_CARD_REPAY_AMT)>0 and (PHONE_REPAYMENT_AMT)>0,'Expenses Including Credit Card Debts',

If(( CREDIT_CARD_REPAY_AMT)>0 and (INS_REPAYMENT_AMT)>0 and (OVERDRAFT_REPAY_AMT)>0 and (PHONE_REPAYMENT_AMT)>0,'Credit Card and Similar Expenses',

'Other Group'))))AS [Household Expenditure],

//If((Contract_Ref_no) and (Customer_name)>1,'Repurchases','First Time Buyers') AS [Repurchases],

if(wildmatch(job_title,'*Advocate*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Doctor*'), 'Medical Doctor',

if(wildmatch(job_title,'*Medical Doctor*'), 'Medical Doctor',

if(wildmatch(job_title,'*Lawyer*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Developer*'), 'IT Practitioner',

if(wildmatch(job_title,'*Programmer*'), 'IT Practitioner',

if(wildmatch(job_title,'*Accountant*'), 'Accountant',

if(wildmatch(job_title,'*Engineer*'), 'Engineer',

if(wildmatch(job_title,'*Owner*'), 'Self Employed',

if(wildmatch(job_title,'*Medical Practitioner*'), 'Medical Doctor',

if(wildmatch(job_title,'*Judge*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Legal Executive*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Family Advocate*'), 'Legal Practitioner',

if(wildmatch(job_title,'*Nurse*'), 'Nurse',

if(wildmatch(job_title,'*Teacher*'), 'Teacher')))))))))))))))

AS [Job Titles]

FROM

[BMW Consolidated SF Data 10032016.xlsx]

(ooxml, embedded labels, table is [BMW (45)]);

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Repur_Check:

LEFT JOIN (App_Status)

LOAD Contract_Ref_no,

Customer_name,

If( Count([Customer_name]) >1, 'Repurchases', 'First Time Buyers') as [Repurchases]

RESIDENT App_Status

GROUP BY Contract_Ref_no, [Customer_name];

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

JobTitle:

LOAD [Job Titles],

[Cars Bought1] as CarBoughtNew,

Count([Cars Bought1]) as CountOfCars

resident App_Status

Group By [Job Titles],[Cars Bought1];

New:

LOAD [Job Titles],

CarBoughtNew,

Autonumber(CountOfCars,[Job Titles]) as Rank

Resident JobTitle

order by [Job Titles],CarBoughtNew desc;

Drop Table JobTitle;

Top3:

noconcatenate

LOAD *

Resident New

where Rank <=3;

Drop Table New;

Final:

LOAD [Job Titles],

Concat(Distinct CarBoughtNew,',') as Top3Cars

Resident Top3

Group by [Job Titles];

Drop Table Top3;