topic rename datas in QlikView

rename datas

Thu, 10 Jun 2010 13:23:31 GMT

Hi every body,

I have a table:

Items

Jouet

Cadeaux

Décoration

Toys

Gift

Decoration

and I want to put Toys into Jouet, Gift into Cadeux and Decoration into Décoration in order to get one and only table which is:

Items

Jouet

Cadeaux

Décoration

whithout loosing any datas

Can someone help me?

rename datas

sparur — Thu, 10 Jun 2010 13:44:17 GMT

Hello, Bob.

What are:

Jouet

Cadeaux

Décoration

Toys

Gift

Decoration

Is it name of fields or it's values of field Items?

rename datas

Anonymous — Thu, 10 Jun 2010 13:54:57 GMT

It doesn't make any sens.

rename datas

Thu, 10 Jun 2010 14:16:17 GMT

HI,

Jouet

Cadeaux

Décoration

Toys

Gift

Decoration

are values of the field items

for exemple a table like this:

Item | Quantity

I finally want the table:

Item | Quantity

Jouet | 25

Cadeaux | 35

Décoration | 45

rename datas

johnw — Sat, 12 Jun 2010 01:04:43 GMT

Perhaps this?

[Translate to French]:
LOAD * INLINE [
From, To
Toys, Jouet
Gift, Cadeaux
Decoration, Décoration
];
LOAD
applymap('Translate to French',Item) as Item
,Quantity
...
;

rename datas

Mon, 14 Jun 2010 10:29:22 GMT

hi,

thanks for your reply, the idea seems to be good, but it is not working, it says there is a Script Error...

Other ideas ???

rename datas

johnw — Mon, 14 Jun 2010 16:45:21 GMT

Ah, oops, I missed a critical key word. Sorry about that.

[Translate to French]:
MAPPING LOAD * INLINE [
From, To
Toys, Jouet
Gift, Cadeaux
Decoration, Décoration
];
LOAD
applymap('Translate to French',Item) as Item
,Quantity
...
;

rename datas

Mon, 14 Jun 2010 17:06:07 GMT

still the same problem.... even when I added the key word MAPPING...

rename datas

johnw — Mon, 14 Jun 2010 17:26:12 GMT

Works for me. See attached.

rename datas

Tue, 15 Jun 2010 13:42:55 GMT

Hi, thanks for your reply.

Lot of thanks for your help 🙂

Now I have an other problem...maybe you can help me if it isn't disturbing you...??? I was supposed to be able to resolve it with the first problem but it doesn't seem to be that easy...

Actually what I really wanted to do is to join 2 columns of 2 different Excel files, for exemple: I have one Excel file which contains:

Item | Quantity

Toys | 20

Gift | 25

Decoration | 30

And an other excel fil which contains:

Item | Quantity

Jouet | 5

Cadeaux | 10

Décoration | 15

And I want in qlikview one table with contains:

Item | Quantity

Jouet | 25

Cadeaux | 35

Décoration | 45

The problem is that those two table are in two different excel files so when I join them, the results (colum quantity) should be the addition of the two but it is not...

rename datas

johnw — Tue, 15 Jun 2010 19:15:40 GMT

I would concatenate the tables instead of joining them, and then use sum(Quantity) to get the sum of the two tables. But if you really want to join them, you're going to need to rename the Quantity field temporarily, because otherwise it will try to join on it and fail, and you'll get a concatenation anyway. And if the join doesn't fail, then you'll have the wrong quantity (half as much as you wanted).

You can do the join like this, if you really want to do that instead of concatenate:

EDIT: That first left join should probably be an outer join.

[Translate to French]:
MAPPING LOAD * INLINE [
From, To
Toys, Jouet
Gift, Cadeaux
Decoration, Décoration
];
[Data]:
LOAD
applymap('Translate to French',Item) as Item
,Quantity as Quantity1
INLINE [
Item, Quantity
Jouet, 5
Cadeaux, 10
Décoration, 15
];
LEFT JOIN ([Data])
LOAD
applymap('Translate to French',Item) as Item
,Quantity as Quantity2
INLINE [
Item, Quantity
Toys, 20
Gift, 25
Decoration, 30
];
LEFT JOIN ([Data])
LOAD
Item
,Quantity1 + Quantity2 as Quantity
RESIDENT [Data]
;
DROP FIELDS
Quantity1
,Quantity2
;

rename datas

Wed, 16 Jun 2010 16:33:42 GMT

Hi,

Thanks again

FInally, I was able to make it with your first reply (mapping). I did the mapping and in the same table I load the second Excel file, so it matched to the other colum 🙂

Now I have a new problem...

I have an excel file with some names in the format : XXXXX, YYYYY and I want to load it in Qlikview in the format: XXXXX YYYYY

Example: I have :

Name

Bob, Bob

Abc, def

Xyz, Utv

and I nedd :

Name

Bob Bob

Abc def

Xyz Utv

Is it possible to remove this "," which is disturbing me ????

rename datas

johnw — Wed, 16 Jun 2010 18:08:44 GMT

Try this:

purgechar(Name,',') as Name

rename datas

Thu, 17 Jun 2010 09:24:15 GMT

Hi, thanks for your help.

That's what I tried first but it was removing the ',' and doesn't put a space between the first name and the last name...

Finally I found out this :

replace (Name, ',' , ' ') as Name

and it works 🙂

But still a small problem, some names got an accent like Michèle in one file and not accent Michele in the other file, is it also possible to remove the accent?

rename datas

johnw — Thu, 17 Jun 2010 18:02:26 GMT

Your example data showed a space after the comma, which is why I gave the solution I did. Anyway, looks like you have it solved now.

As for making the names match, I often use a map to help with data clean up and presentation:

[Text Map]:
LOAD * INLINE [
From, To
Mcdonald,McDonald
Michèle, Michele
Usa, USA
...
];

mapsubstring('Text Map',Field) as Field

Actually, our source data is often in all caps, and sometimes unreliably so, so more typically I do this:

mapsubstring('Text Map',capitalize(Field)) as Field

If you want to replace ANY accent marks, you could just list all of the accented characters and their unaccented equivalent in the text map, as opposed to specific names and words you want to fix.

rename datas

Fri, 18 Jun 2010 10:06:10 GMT

oh ok, i'm sorry, there was no space after the comma that was my fault... But yes it is resolved thanks

And thanks for your answer concerning the accent I used the second choice (list of all accented characters) and it worked well

Thank you very much for your help 🙂