starts with QV

[master_student]

Hello,

What is the synonym of start by function in qlikview?

If (CDR start by:5, MARKETING EXPENSES, ADMIN OPEX)

Thanks

[rubenmarin]

I think it will be better if you explain what are you trying to do... It's on a chart to make a expression to conditionally sum values? It's to fill a field while executing script?...

[migueldelval]

Ok master student,

Your are trying to make that if CDR starts by "5", value of "Opexcategory" will be "MARKETING EXPENSES", If not value of "Opexcategory" will be "ADMIN OPEX".  Is it?

I think that it´s better make it in script, but I´m going to think about this.

Regars

Miguel del Valle.

[master_student]

yes Miguel, this is what I mean. could you tell me how to do it in the script?

Thanks

[rubenmarin]

Hi, in the script you can add the calculated field in the same table you laod the CDR field:

LOAD Fields,

     If (WildMatch(CDR, '5*'), 'MARKETING EXPENSES', 'ADMIN OPEX') as opexCategory,

     MoreFields

FROM/SELECT ...

[master_student]

Thanks Ruben,

How to display also opex category of CDR that don't match those conditions?

[master_student]

I will explain my issue,

I have a CDR WITH opex category, no problem with those ones,

the problem with the cdr that don't have an opex category, if those ones starts with 5 then opexcategory=MARKETING EXPENSES else opexcategory=ADMIN OPEX

how to do that? in the script or in the table expression? it is a little bit confused.

If (opexcategory =' ' and WildMatch(CDR, '5*'), 'MARKETING EXPENSES', 'ADMIN OPEX')

The result is a table with

CDR / OPEXCATEGORY

Please help.

[rubenmarin]

OK, so if opexcategory has a value, it will keep the value and, in case it doesn't have any value, check the CDR, if it starts by '5' set 'MARKETING EXPENSES' in other case set 'ADMIN OPEX')

It will be better to have it calculated in script, your last expression was close but there is a difference between '' and Null(), because Null() means doesn't exist any value, but '' means an empty value (it's empty but is not Null()). You can check this in a text box with:

If(Null()='', 1, 0) // It will return zero, meaning they are different values.

To check both cases in one comparison you can use:

If(Len(Trim(CDR))=0 and WildMatch(CDR, '5*'), 'MARKETING EXPENSES', 'ADMIN OPEX')

It will also check the ' ' (one -or several- blank space) and other special characters.

[master_student]

in the field expression, I put :

If ((isnull(OpEx_Category)and WildMatch(CDR, '5*')), 'MARKETING EXPENSES', OpEx_Category). it works fine

but the problem when the CDR don't start with 5 and opex category is null. see bellow

How to put the two conditions in the same expression?

Thanks

[rubenmarin · Accepted solution]

Sorry, my last answer was wrong, I didn't counted the option to maintain the value, it can be:

If(Len(Trim(OpEx_Category))>0, OpEx_Category, //If Opexcategory has a value, keep the value

If(WildMatch(CDR, '5*'), 'MARKETING EXPENSES', //If doesnt' have a value, check the start of CDR to set the value

  'ADMIN OPEX'))

[master_student]

Thanks Ruben. It works