topic Re: Rename records based on values in QlikView

Rename records based on values

Anonymous — Tue, 15 Dec 2015 18:29:56 GMT

Is there a way in the script to rename records based on values. i.e. in the field 'Type', I want any 1 to equal 'Private', 2 to equal 'General', etc.

Thank you!

Re: Rename records based on values

Anonymous — Tue, 15 Dec 2015 18:48:15 GMT

Have a look at the ApplyMap() function.

This Blog Post Joins and Lookups explains it as well as a few other things.

Re: Rename records based on values

sergiorey — Tue, 15 Dec 2015 18:48:57 GMT

Hi Tyler,

You can do it this way:

If([Type]=1, 'Private', If([Type]=2, 'General', [Type])) As [Type]

Hope it helps.

Regards,

Sergio.

Re: Rename records based on values

sunny_talwar — Tue, 15 Dec 2015 18:50:50 GMT

Try this:

Pick(Match(Type, 'Private', 'General'), 1, 2) as NewType

Re: Rename records based on values

eduardo_sommer — Tue, 15 Dec 2015 18:51:57 GMT

Use an ApplyMap function

First, load the table with the from/to columns

FromTo:

Mapping load * inline [

From, To

1, Private

2, General

...

];

Table:

Load

Field1,

Field2,

ApplyMap('FromTo', Type, 'Default Value') as Type,

...

From ...;

Eduardo

Re: Rename records based on values

Anonymous — Tue, 15 Dec 2015 19:49:53 GMT

None of these are working unfortunately.

It tells me that it does not recognize the 'Type' field when I apply these methods. Any other thoughts?

Re: Rename records based on values

sergiorey — Tue, 15 Dec 2015 20:12:31 GMT

Is it working when loading "Type" field without transformations?

Re: Rename records based on values

sunny_talwar — Tue, 15 Dec 2015 20:46:26 GMT

So you don't have a field called Type. What is the name of the field that contains Private, General etc?

Re: Rename records based on values

rwunderlich — Tue, 15 Dec 2015 21:04:17 GMT

Can you post your script?

Re: Rename records based on values

MarcoWedel — Tue, 15 Dec 2015 23:52:44 GMT

Hi,

I guess this thread is related to your previous thread: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

So maybe you are having a field [Type of PII] instead you could use in those expressions?

hope this helps

regards

Marco

Re: Rename records based on values

Anonymous — Wed, 16 Dec 2015 16:59:43 GMT

The field is called 'Type of PII' and has values 1 through 5.

I want to assign the following:

1 = General PII

2 = Sensitive PII

3 = Financial Information

4 = Medical Information

5 = Multiple Types

Re: Rename records based on values

eduardo_sommer — Wed, 16 Dec 2015 17:20:52 GMT

Adapting my former post:

Use an ApplyMap function

First, load the table with the from/to columns

FromTo:

Mapping load * inline [

From, To

1,General PII

2,Sensitive PII

3,Financial Information

4,Medical Information

5,Multiple Types

...

];

Table:

Load

Field1,

Field2,

ApplyMap('FromTo', [Type of PII], 'Default Value') as [Type of PII],

...

From ...;

Eduardo

Re: Rename records based on values

Anonymous — Wed, 16 Dec 2015 17:29:24 GMT

Thank you!

To be clear...I load this first in my script...

FromTo:

Mapping load * inline [

From, To

1,General PII

2,Sensitive PII

3,Financial Information

4,Medical Information

5,Multiple Types]

Then I load my table but where the [Type of PII] field would be, I put this instead?

ApplyMap('FromTo', [Type of PII], 'Default Value') as [Type of PII],

Re: Rename records based on values

eduardo_sommer — Wed, 16 Dec 2015 17:38:08 GMT

Exactly

Re: Rename records based on values

Anonymous — Wed, 16 Dec 2015 17:48:47 GMT

I get the following error message when applying that logic...

Garbage after statement

FromTo:

Mapping load * inline [

From, To

1,General PII

2,Sensitive PII

3,Financial Information

4,Medical Information

5,Multiple Types]

Re: Rename records based on values

sunny_talwar — Wed, 16 Dec 2015 17:57:29 GMT

Did you add semi-colon (;) after ] at the end?

FromTo:

Mapping

LOAD * Inline [

From, To

1,General PII

2,Sensitive PII

3,Financial Information

4,Medical Information

5,Multiple Types

];

Re: Rename records based on values

Anonymous — Wed, 16 Dec 2015 18:05:41 GMT

It worked, thank you!

Re: Rename records based on values

sunny_talwar — Wed, 16 Dec 2015 18:09:43 GMT

Awesome .

If you are satisfied, please close the thread by marking correct and/or helpful answers.

Best,

Sunny