topic Re: Change value on load in QlikView

Change value on load

Wed, 18 Jun 2014 11:00:01 GMT

Hi Everyone,

I'm quite a newbie to QlikView and need help with the following question:

Is it possible to change values/display values as another value?

I did already for the column names but the values are now A1, A2, A3 or A4 but I want it to be 1,2,3 or 4 for some KPI stats.

I've attached an image of the table and on the right side how I want it to be (if possible).

My script right now (using MySQL):

LOAD

id as ID,

submitdate as [Datum ingevuld],

startlanguage as Taal,

`326461X66X657` as Leraar,

`326461X66X658` as Klas,

`326461X62X6231` as 1,

`326461X62X6232` as 2,

`326461X62X6233` as 3,

`326461X62X6234` as 4,

`326461X62X6235` as 5,

`326461X62X6236` as 6,

`326461X62X6237` as 7,

`326461X62X6238` as 8,

`326461X62X6239` as 9,

`326461X62X62310` as 10,

`326461X63X62411` as 11,

`326461X63X62412` as 12,

`326461X63X62413` as 13,

`326461X63X62414` as 14,

`326461X63X62415` as 15,

`326461X63X62416` as 16,

`326461X63X62417` as 17,

`326461X63X62418` as 18,

`326461X63X62419` as 19,

`326461X63X62420` as 20,

`326461X63X62421` as 21,

`326461X63X62422` as 22,

`326461X63X62423` as 23,

`326461X63X62424` as 24,

`326461X63X62425` as 25,

`326461X64X62526` as 26,

`326461X64X62527` as 27,

`326461X64X62528` as 28,

`326461X64X62529` as 29,

`326461X65X626` as Opmerkingen;

ODBC CONNECT TO [OC Enquete];

SQL SELECT id,

submitdate,

startlanguage,

`326461X66X657`,

`326461X66X658`,

`326461X62X6231`,

`326461X62X6232`,

`326461X62X6233`,

`326461X62X6234`,

`326461X62X6235`,

`326461X62X6236`,

`326461X62X6237`,

`326461X62X6238`,

`326461X62X6239`,

`326461X62X62310`,

`326461X63X62411`,

`326461X63X62412`,

`326461X63X62413`,

`326461X63X62414`,

`326461X63X62415`,

`326461X63X62416`,

`326461X63X62417`,

`326461X63X62418`,

`326461X63X62419`,

`326461X63X62420`,

`326461X63X62421`,

`326461X63X62422`,

`326461X63X62423`,

`326461X63X62424`,

`326461X63X62425`,

`326461X64X62526`,

`326461X64X62527`,

`326461X64X62528`,

`326461X64X62529`,

`326461X65X626`

FROM `DB`.`Table` WHERE `submitdate` IS NOT NULL;

Re: Change value on load

Anonymous — Wed, 18 Jun 2014 11:04:34 GMT

To remove letters form a value and keep only numbers, you can use:

keepchar(FieldName, '0123456789') as NewFieldName

And, there is no image attached...

Re: Change value on load

tresB — Wed, 18 Jun 2014 11:13:42 GMT

Or, if it is only prefixed by 'A' for all values, you can try PurgeChar() like:

Load

PurgeChar(YourField, 'A') as YourNewFieldName

Re: Change value on load

Wed, 18 Jun 2014 11:29:53 GMT

Thanks but it's not working...

I've tried the following scripts:

LOAD

`326461X62X6231` as 1,

purgechar(1, 'A') as New1;

and

LOAD

purgechar(`326461X62X6231`, 'A') as New1;

But neither one of them is actually working.

Re: Change value on load

tresB — Wed, 18 Jun 2014 11:32:25 GMT

Try like:

LOAD

purgechar(326461X62X6231, 'A') as New1;

Re: Change value on load

Wed, 18 Jun 2014 11:33:14 GMT

Thanks but it's not working, same as tresesco's solution (see reply).

And I also attached the image now.

Re: Change value on load

Wed, 18 Jun 2014 11:35:52 GMT

Then I get the following error (attachment).

Re: Change value on load

its_anandrjs — Wed, 18 Jun 2014 11:37:27 GMT

By using this purgechar('326461X62X6231', 'A') it removes A but why you use A because in string 326461X62X6231 there is no A so there is nothing out put let me know.

Re: Change value on load

Wed, 18 Jun 2014 11:40:01 GMT

In the "column name" there is no A but in the records in column 326461X62X6231 there are values like A1, A2, A3 or A4, maybe this helps...

If you need screenshots or further info please let me know.

Re: Change value on load

tresB — Wed, 18 Jun 2014 11:41:37 GMT

If you use:

LOAD

purgechar(`326461X62X6231`, 'A') as New1

What are the values you are getting in New1 field?

Re: Change value on load

its_anandrjs — Wed, 18 Jun 2014 11:43:48 GMT

Then use it like

LOAD

Purgechar( YourFieldName, 'A') as NewField

From Location;

This is right

Re: Change value on load

Wed, 18 Jun 2014 12:25:56 GMT

Oh I made a stupid mistake, your solution is actually working but I forgot to include the field into the table, Thanks!