Change value on load

[#1]

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;

[Anonymous]

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

keepchar(FieldName, '0123456789') as NewFieldName

And, there is no image attached...

[tresesco · Accepted solution]

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

Load

          PurgeChar(YourField, 'A') as YourNewFieldName

[#4]

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.

[tresesco]

Try like:

LOAD

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

[#6]

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

And I also attached the image now.

[#7]

Then I get the following error (attachment).

[its_anandrjs]

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.

[#9]

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.

[tresesco]

If you use:

LOAD

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

What are the values you are getting in New1 field?