Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hello,
You can write directly in the script and use.
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
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
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.
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;
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