topic Re: Using match to select specific items and renaming in QlikView

Using match to select specific items and renaming

Tue, 23 Feb 2016 13:12:06 GMT

Hello,

How do I match to specific item on my dimension. also wanna collate some item together and name them as one. see...

=if(match([item text], 'Blocks', 'Buckets', 'Box',

if(match([item text], 'Cutlass', 'Plates','Spoon'), 'Cutlery'),[Item text]),
'Cloth','Table','Chair'.........''

),[item text])

I wanna collate cutlass, plate, spoon to one name. what so I do?

Re: Using match to select specific items and renaming

hic — Tue, 23 Feb 2016 13:16:25 GMT

Use a mapping table. See Data Cleansing

HIC

Re: Using match to select specific items and renaming

sunny_talwar — Tue, 23 Feb 2016 13:16:37 GMT

Not sure what you are trying to do? May be this in your load statement:

If(Match([item text], 'Cutlass', 'Plates', 'Spoon'), 'Cutlery', [item text]) as New_Item_Text

Re: Using match to select specific items and renaming

maxgro — Tue, 23 Feb 2016 13:17:35 GMT

load

[item text],

if(match([item text], 'Blocks', 'Buckets', 'Box',), '????',

if(match([item text], 'Cutlass', 'Plates','Spoon'), 'Cutlery',

[item text]

)) as [new item text]

inline [

item text

Blocks

Buckets

Box

Cutlass

Plates

Spoon

Chair

];

Re: Using match to select specific items and renaming

jonathandienst — Tue, 23 Feb 2016 13:18:35 GMT

You can do this:

=If(Match([item text], 'Blocks', 'Buckets'), 'Box',

If(Match([item text], 'Cutlass', 'Plates','Spoon'), 'Cutlery',

If(Match([item text], 'Cloth','Table','Chair'), 'Furniture')))

But I would create an mapping table that loads the category associated with the items, and then use the category as the dimension.

Re: Using match to select specific items and renaming

Anonymous — Tue, 23 Feb 2016 13:23:53 GMT

You can use an excel file or an inline table to load the category to the corresponding item.

Load it as a mapping table then use the applymap function in your destination table.

Re: Using match to select specific items and renaming

Tue, 23 Feb 2016 14:02:32 GMT

Its a nested IF statement. I already matched to pick specific fields and I need to call these items A name. so that is where the next IF statement comes in

Re: Using match to select specific items and renaming

Tue, 23 Feb 2016 14:03:53 GMT

Am coding in Dimension editor not load please. I have a field holding all the items I wanna match

Re: Using match to select specific items and renaming

Tue, 23 Feb 2016 14:04:24 GMT

No that's not what I want

Re: Using match to select specific items and renaming

Tue, 23 Feb 2016 14:15:07 GMT

How do I do nested IF here

IF(Match(Country, 'Great Britain','UNITED KINGDOM','UK'), 'United Kingdom', Country)

Re: Using match to select specific items and renaming

hic — Tue, 23 Feb 2016 14:23:01 GMT

You can do a

=If(Match(Country, 'Great Britain','UNITED KINGDOM','UK'), 'United Kingdom',

If(Match(Country, 'Paris','Bretagne'), 'France'))

Just make sure that you have the same number of right-brackets in the end as you have If()-functions.

HIC

PS But a mapping table is MUCH better...

Re: Using match to select specific items and renaming

hic — Tue, 23 Feb 2016 14:26:24 GMT

I guess you are using this in a calculated dimension. If so, you might need to wrap in in Aggr() function (which is kind of like a for-next loop):

=Aggr(

If(Match(Country, 'Great Britain','UNITED KINGDOM','UK'), 'United Kingdom',

If(Match(Country, 'Paris','Bretagne'), 'France')),

Country)

HIC

Re: Using match to select specific items and renaming

Tue, 23 Feb 2016 14:32:15 GMT

Mapping table? Make me understand

Re: Using match to select specific items and renaming

sunny_talwar — Tue, 23 Feb 2016 14:34:51 GMT

Mapping load information can be found here as well:

http://www.learnqlickview.com/a-qlikview-tutorial-mapping-load-in-qlikview/

Re: Using match to select specific items and renaming

hic — Tue, 23 Feb 2016 14:57:21 GMT

See Data Cleansing