topic Re: Converting numbers to text, 1 = one ... in QlikView

Converting numbers to text, 1 = one ...

sibrulotte — Fri, 14 Mar 2014 22:37:19 GMT

Hi,

so I would like to show the number of periods as numbers spelled out for proper presentation. To top it off, in french!

I loaded inline this table as a data island:

Chiffres:

Load * inline [
Chiffremois, Nombremois
1, un
2, deux
3, trois
4, quatre
5, cinq
6, six
7, sept
8, huit
9, neuf
10, dix

11, onze
12, douze
];

So you know, that's pretty french, but you can figure out what the english version is...

I figured I'd do a plain set analysis like this get back my "Nombremois"

but it's not working out.

=Only({<Chiffremois=[Période financière]>} Nombremois)

BTW, [Période financière] by itself gives me a 9 in a text box. No alternative states are involved.

Yet I still can't get it to return 'neuf' ...

Anybody want to contribute/lend a hand?

Re: Converting numbers to text, 1 = one ...

MK_QSL — Fri, 14 Mar 2014 23:00:26 GMT

=Only({<Chiffremois=['Période financière']>} Nombremois)

=Only({<Chiffremois=["Période financière"]>} Nombremois)

if Période financière is variable

=Only({<Chiffremois=['$(=Période financière)']>} Nombremois)

Re: Converting numbers to text, 1 = one ...

chiru_thota — Sat, 15 Mar 2014 00:07:54 GMT

=Only({<Chiffremois=[Chiffremois]>} Nombremois)

Re: Converting numbers to text, 1 = one ...

PrashantSangle — Sat, 15 Mar 2014 06:46:14 GMT

Hi,

You can do this at script level

rename your field

[Période financière] as Chiffremois

so that your table link with inline table

Re: Converting numbers to text, 1 = one ...

maxgro — Sat, 15 Mar 2014 11:10:22 GMT

why not a dual for your [Période financière]?

from online help

dual( s , x )

Association forcée d'une représentation de chaîne arbitraire s à une représentation numérique donnée x. Dans QlikView, lorsque plusieurs éléments de données lus dans un champ ont différentes représentations de chaîne mais la même représentation numérique valide, ils partagent tous la première représentation de chaîne rencontrée. Cette fonction peut s'utiliser aussi bien dans les scripts que dans les expressions de graphique.

Dans les scripts, la fonction dual s'emploie généralement relativement tôt dans le script, avant que d'autres données ne soient lues dans le champ concerné, afin de créer cette première représentation de chaîne qui sera affichée dans les listes de sélection, etc.

Remarque :
Si une valeur dual est trop grande pour tenir dans un objet de type champ, elle est représentée par des signes ## au lieu d'être tronquée et représentée par des points de suspension (...) comme une chaîne.

Exemple (dans un script) :

Load dual ( string,numrep ) as DayOfWeek inline

[ string,numrep

Monday,0

Tuesday,1

Wednesday,2

Thursday,3

Friday,4

Saturday,5

Sunday,6 ];

load Date, weekday(Date) as DayOfWeek from afile.csv;

Cet exemple de script génère un champ DayOfWeek avec les jours de la semaine écrits en toutes lettres. QlikView considérera toujours ce champ comme un champ numérique.

Re: Converting numbers to text, 1 = one ...

Miguel_Angel_Baeyens — Sat, 15 Mar 2014 13:11:57 GMT

Hi,

Yes, Dual() is the best option to get what you want. You can load it INLINE or from a table or a file.

Miguel

Re: Converting numbers to text, 1 = one ...

sibrulotte — Mon, 17 Mar 2014 15:31:44 GMT

Thank you for the cue.

When I load this:

Chiffres:

Load * inline [
Période financière, Nombremois
1, un
2, deux
3, trois
4, quatre
5, cinq
6, six
7, sept
8, huit
9, neuf
10, dix
11, onze
12, douze
];

CalendrierComptable:
LOAD
[Année financière],
dual(ApplyMap('Chiffres', [Période financière]),[Période financière] ) as [Période financière]

From $(QVDDir)\CalendrierComptable.qvd (

qvd);

This returns nulls for Période financière in my Calendar. what am I missing...

My number is Période financière, my text is Nombremois, mapped through Chiffres using Période finaicière.

Got it.

Well I guess I didn't get it that well...

Re: Re: Converting numbers to text, 1 = one ...

maxgro — Mon, 17 Mar 2014 15:54:22 GMT

to use ApplyMap you need a special load, a Mapping load

Chiffres:

Mapping Load * inline [
Période financière

..........

Re: Converting numbers to text, 1 = one ...

sibrulotte — Mon, 17 Mar 2014 18:25:01 GMT

Yes in deed, thank you.

Do you know a way around arythmetic functions done to that field?

I.e Text(Période financière - 1), will not return the text but the number...

Re: Converting numbers to text, 1 = one ...

stevedark — Mon, 17 Mar 2014 19:00:34 GMT

For this you will need a variable that receives a parameter that then looks up the text. If you search for QlikView Parameters with Variables you should find info on this, I know Stephen Redmond did a good article on this a while back.

The variable would need to contain:

='maxstring({<Integer={'$1'}>}StringNumber)'

Where, you have a data island of integer and strings. The variable call will be like:

=$(vIntToString(10))

Not too sure how this will behave if you put an expression in here though, eg:

=$(vIntToString([Période financière] - 1))

My gut feel is that it will not work though. An interesting problem!

Re: Converting numbers to text, 1 = one ...

stephencredmond — Mon, 17 Mar 2014 22:59:33 GMT

Sorry, arriving late to this party. I have noticed that your syntax is incorrect for the Set:

=Only({<Chiffremois={$(=[Période financière])}>} Nombremois)

Should give you the text that matches if the Période financière is a variable. When I test this and set it to 9, the text I get is "neuf".

=Only({<Chiffremois={$(=[Période financière]-1)}>} Nombremois)

Will give "huit".

Of course, what happens when the variable is set to 1! A simple if would work:

=Only({<Chiffremois={$(=If([Période financière]=1,12,[Période financière]-1))}>} Nombremois)

Regards,

Stephen

Re: Converting numbers to text, 1 = one ...

sibrulotte — Tue, 18 Mar 2014 13:40:42 GMT

Hi Période financière was not a variable. I tried storing the value in a vPériodefinancière variable to test what you were stating and I did not get a proper result for the above mentionned synthax with a -1.

I'll try and test it later when I actually have time to experiment further.

Also, this relates to a question I was asking with alternate states:

http://community.qlik.com/thread/109605

I am trying to retrieve a selection from the default alternate state and storing it to a variable to use in a set analysis of a "comparative" alternate state. If you find something there, it seems I'm never able to use variables in set analysis, and I'm getting frustrated .

last thing:

http://knowyourmeme.com/photos/653707-tuna-the-chiweenie

I laughed so hard when I read this, I thought you might also chukle at it

Re: Re: Converting numbers to text, 1 = one ...

stephencredmond — Tue, 18 Mar 2014 21:56:18 GMT

I guess that I am still not exactly sure what it is that you want to achieve.

If you look at the attached application, it shows some playing with disconnected data using Set Analysis. I don't know if it is close to what you want.

Perhaps, if you step back from the specific problem and describe what you want to have happen then we can recommend a good approach?

Regards,

Stephen

Re: Re: Re: Converting numbers to text, 1 = one ...

sibrulotte — Wed, 19 Mar 2014 15:52:10 GMT

that actually works, I had just forgot that my initial load inline was done with a key to the calendar. and never went back to it. I took out the key, and the function works great.

Now, I added a test, if Période is lower than 7, no substarction, otherwise, substract 6.

See file.

6 works, but anything below returns null.

Any idea what is wrong there?

Re: Converting numbers to text, 1 = one ...

stephencredmond — Fri, 21 Mar 2014 16:20:56 GMT

Hi,

Because one of your expressions resulted in a return value of -1, this is invalid in a numeric set and needs to be quoted - even though that isn't the set that is used!

=if([Période financière]<7,Concat({<Chiffremois={$(=Max([Période financière]))}>} Nombremois, ',', Chiffremois),Concat({<Chiffremois={"$(=Max([Période financière]-6))"}>} Nombremois, ',', Chiffremois))

Stephen

Re: Converting numbers to text, 1 = one ...

sibrulotte — Fri, 21 Mar 2014 16:44:16 GMT

So I'm pretty much screwed, unless I set variables ahead according to the selection value I guess.

That's not going to happen since the boss said he prefers numbers now...

I love my job.