Concat Function To Get Top Performers - Qlik Community

kaygee28 · ‎2016-03-29

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.

kaygee28 · ‎2016-03-29

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.

sunny_talwar · ‎2016-03-29

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

sunny_talwar · ‎2016-03-29

or share the script that you are using?

kaygee28 · ‎2016-03-29

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.

sunny_talwar · ‎2016-03-29

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

Report Inappropriate Content · ‎2016-03-29

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

kaygee28 · ‎2016-03-29

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.

kaygee28 · ‎2016-03-29

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

Report Inappropriate Content · ‎2016-03-30

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.