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

Assigning a numerical value to text strings in Qlikview

Tue, 03 Sep 2013 19:29:55 GMT

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

Re: Assigning a numerical value to text strings in Qlikview

Anonymous — Tue, 03 Sep 2013 19:39:55 GMT

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

Re: Assigning a numerical value to text strings in Qlikview

Fri, 06 Sep 2013 12:47:59 GMT

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

Re: Assigning a numerical value to text strings in Qlikview

Fri, 06 Sep 2013 13:26:35 GMT

Hello,

You can write directly in the script and use.

Re: Assigning a numerical value to text strings in Qlikview

Vegar — Fri, 06 Sep 2013 13:39:26 GMT

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

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

Anonymous — Fri, 06 Sep 2013 13:54:34 GMT

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

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

Vegar — Fri, 06 Sep 2013 14:57:30 GMT

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;

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

Fri, 13 Sep 2013 19:34:22 GMT

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