Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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;

Tags (3)
1 Solution

Accepted Solutions
tresesco
Not applicable

Re: Change value on load

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

Load

          PurgeChar(YourField, 'A') as YourNewFieldName

11 Replies
mov
Not applicable

Re: Change value on load

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
Not applicable

Re: Change value on load

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

Load

          PurgeChar(YourField, 'A') as YourNewFieldName

Not applicable

Re: Change value on load

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
Not applicable

Re: Change value on load

Try like:

LOAD

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

Not applicable

Re: Change value on load

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

And I also attached the image now.

Not applicable

Re: Change value on load

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

its_anandrjs
Not applicable

Re: Change value on load

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

Re: Change value on load

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
Not applicable

Re: Change value on load

If you use:

LOAD

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

What are the values you are getting in New1 field?