topic Re: start by QV in QlikView

starts with QV

master_student — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

What is the synonym of start by function in qlikview?

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

Thanks

Re: start by QV

tresB — Mon, 06 Jun 2016 10:39:53 GMT

What does 'start by' do here? Could you please explain your expected output here? May be a qv solution could be suggested then.

Re: start by QV

migueldelval — Mon, 06 Jun 2016 10:42:29 GMT

Hi master_student,

What dou you want to say with "start by"?. Maybe you can try CDR={"5*"}, but I´m not sure if this it´s correct for you. Try to explain it.

Regards

Re: start by QV

rubenmarin — Mon, 06 Jun 2016 10:49:01 GMT

I don't know what that 'start by' does. It checks if CDR starts by the character '5'?

In that case instead of 'CDR start by:5' you can use:

- WildMatch(CDR, '5*')

- Left(CDR, 1)='5'

- CDR like '5*'

Re: start by QV

awhitfield — Mon, 06 Jun 2016 10:56:07 GMT

Sorry, but t's really not clear what you mean here. can you expand on your requirements?

Andy

Re: start by QV

master_student — Mon, 06 Jun 2016 11:11:49 GMT

if the cdr field strats with the character 5. and I need it in an if statment like that :

If (CDR start with the character :5, then opex =MARKETING EXPENSES else opex=ADMIN OPEX)

Re: start by QV

master_student — Mon, 06 Jun 2016 11:14:18 GMT

if the cdr field strats with the character 5. and I neesd it in an if statment like that :

If (CDR start with the character :5, then opex =MARKETING EXPENSES else opex=ADMIN OPEX)

Thanks

Re: start by QV

rubenmarin — Mon, 06 Jun 2016 11:15:40 GMT

Ok, have you tried the options above? In example:

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

Re: start by QV

migueldelval — Mon, 06 Jun 2016 11:30:29 GMT

Hi master_student,

I think that rubenmarin‌‌ gave you the correct answer, only you need to add ' to the expression.

=If (WildMatch([texto], '5*'), 'MARKETING EXPENSES', 'ADMIN OPEX')

Regards

Miguel

Re: start by QV

master_student — Mon, 06 Jun 2016 13:28:11 GMT

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

opexcategory is anthoer field

is that correct?

Re: start by QV

rubenmarin — Mon, 06 Jun 2016 13:32:43 GMT

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?...

Re: start by QV

migueldelval — Mon, 06 Jun 2016 13:36:47 GMT

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.

Re: start by QV

master_student — Mon, 06 Jun 2016 14:05:25 GMT

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

Thanks

Re: start by QV

rubenmarin — Mon, 06 Jun 2016 14:52:50 GMT

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 ...

Re: start by QV

master_student — Mon, 06 Jun 2016 22:19:15 GMT

Thanks Ruben,

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

Re: start by QV

master_student — Tue, 07 Jun 2016 08:05:03 GMT

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.

Re: starts with QV

rubenmarin — Tue, 07 Jun 2016 09:06:42 GMT

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.

Re: starts with QV

master_student — Tue, 07 Jun 2016 09:37:01 GMT

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

Re: starts with QV

rubenmarin — Tue, 07 Jun 2016 09:40:56 GMT

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'))

Re: starts with QV

master_student — Tue, 07 Jun 2016 09:59:00 GMT

Thanks Ruben. It works