Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Change value on load

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;

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

Load

          PurgeChar(YourField, 'A') as YourNewFieldName

View solution in original post

11 Replies
Anonymous
Not applicable
Author

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
MVP
MVP

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

Load

          PurgeChar(YourField, 'A') as YourNewFieldName

Not applicable
Author

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
MVP
MVP

Try like:

LOAD

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

Not applicable
Author

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

And I also attached the image now.

Not applicable
Author

Then I get the following error (attachment).error.jpg

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.

Not applicable
Author

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
MVP
MVP

If you use:

LOAD

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

What are the values you are getting in New1 field?