Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
