Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kaygee28
Contributor III
Contributor III

Concat Function To Get Top Performers

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.

17 Replies
kaygee28
Contributor III
Contributor III
Author

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

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

sunny_talwar

or share the script that you are using?

kaygee28
Contributor III
Contributor III
Author

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

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

Not applicable

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

Not applicable

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.