Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Assigning a numerical value to text strings in Qlikview

Hi All,

I'm hoping qlikview is able to do the following so that I dont have to do it in excel first and wondered if anyone here might be able to provide any assistance?

I have some data with a 5 possible answers as shown below

Name          How has your weight changed over the last year?    
Arnold          Increased A lot

Jenny          Increased a little

Max               Not changed

Heather          Decreased a little

Ron               Decreased a lot

What I want to do is show these on a guage using by assigning each possible answer a number, so Increased a lot = 2, increased a little = 1, no change = 0 etc into -1 and -2.

Is there any way I can do this In qlikview?

Ideall I would like to add it as an extra field so can I incorporate into the load script?

Any help would be greatly appreciated.

Thanks,

Andrew

7 Replies
mov
Not applicable

Re: Assigning a numerical value to text strings in Qlikview

Andrew, you can do it using dual() function.  No need for an additional field.  Can be done with mapping or with 4-level "if".

With map:

MyMap:

MAPPING LOAD * INLINE[

A,                          B

Increased A lot,       2  

Increased a little,      1

Not changed,          0

Decreased a little,     -1

Decreased a lot,          -2

];

data:

LOAD

dual([How has], applymap('MyMap',[How has])) as [How has],

...

The new [How has] is the text and the number at the same time.

Regards,

Michael

Not applicable

Re: Assigning a numerical value to text strings in Qlikview

Hi Michael,

This sounds great but I'm not sure how the mapping works? (not done mapping before)

Are you saying i need to create an excel file (or similar) called MyMap? or can i write this directly into the script?

Sorry about this, but clarity on how to do the map would be great.

thanks,

Andrew

Not applicable

Re: Assigning a numerical value to text strings in Qlikview

Hello,

You can write directly in the script and use.

Vegar
Not applicable

Re: Assigning a numerical value to text strings in Qlikview

You can use Dual() values.


Here is one way of doing it.


CreateDuals:

LOAD dual(text,number) as YourFieldName INLINE [

text, number

Increased a lot, 2

increased a little,1

no change = 0 ];



Load the rest of your datamodel.


Drop the tabl CreateDuals.


/Vegar

Please ekskuse my Norglish and Swenglish typos.
mov
Not applicable

Re: Re: Assigning a numerical value to text strings in Qlikview

Vegar Lie Arntsen,

Technically it is fine, but not good from the data modeling point - you create additional table which is unnecessary.  Dual is better.

Andrew,

See example attached.  notice taht I have two lisy boxes for Answer - one is in text fromat, another in numeric, and it is the same field.

Regards,

Michael

Vegar
Not applicable

Re: Re: Re: Assigning a numerical value to text strings in Qlikview

I missed the point that my solution only works if you have the numeric values in the transaction table (not the text) and you want to introduce a dual text value. So in this case it will not work, the applymap solution is one way to go.

Michael Solomovich

I think you misunderstood the solution I presented. Yes I introduce a table in the model, but it is only temporary. I delete it after population my field with transaction data. The applymap-method is much slower and breaks the optimized load of qvds, the predefined dual method is faster and does not break optimized load.

Example code:

MyTempDualMaker:

LOAD dual(A,B) as Answer INLINE [

A, B

Increased A lot, 2 

Increased a little, 1

Not changed, 0

Decreased a little, -1

Decreased a lot, -2];

YourDataSource:

LOAD * INLINE [

Name, Answer 

Arnold, 2

Jenny, 1

Max, 0

Heather, -1

Vegar, 0

Ron, 2

];

DROP TABLE MyTempDualMaker;

Please ekskuse my Norglish and Swenglish typos.
Not applicable

Re: Re: Re: Assigning a numerical value to text strings in Qlikview

Hi both,

thanks for all your help, but really not getting on very well with this after scouring through everything youve said.

I've added this to the load script:

 

MyMap:

LOAD dual(A,B) as [UncertaintyComparedToLastYear] INLINE [
A, B
Increased significantly, 2
Increased somewhat, 1
Broadly unchanged, 0
Declined somewhat, -1
Declined significantly, -2
]
;

<<<Then I carry on loading my data.... there are several tables>>>

Directory; 

LOAD

[Unique ID],
Year (Created) as Year,

Name,
[Email address],
Industry,
UncertaintyComparedToLastYear,

It just doesnt seem to be working... can you help?

the uncertainty field is the one I want to have mapped - you can see the 5 possilbe options from the MyMap

Kind regards and thanks for your help,

Andrew