topic Re: Data transformation in QlikView

Data transformation

0li5a3a — Thu, 01 Sep 2016 09:31:18 GMT

Hi Guys,

I need to rename some information in my column and I don't know.

This is my example

TEST1:

Load

id,

[voucher no],

description

from

abc.qvd

In the @description column I got information like :

AB20

BA20,

20-11-16 20 pounds,

24-12-2015 15 pounds,

13 pounds

I want to rename all the description with this format (20-11-16 20 pounds, 24-12-2015 15 pounds,13 pounds) rename ReABC because later I need to make a chart and I need to count all the ReABC.

Please advise me!

Best Regards,

Costy

Re: Data transformation

antoniotiman — Thu, 01 Sep 2016 10:00:03 GMT

LOAD *,

If(description Like '*pounds*',description) as ReABC

from ABC.qvd;

Regards,

Antonio

Re: Data transformation

antoniotiman — Thu, 01 Sep 2016 10:10:27 GMT

If You don't need description field then

TEST1:

Load

id,

[voucher no],

description as ReABC

from

abc.qvd

Where description Like '*pounds*' ;

Re: Data transformation

Chanty4u — Thu, 01 Sep 2016 10:14:01 GMT

try this

TEST1:

LOAD * INLINE [

id, description

1, AB20

2, BA20

3, 20-11-16 20 pounds

4, 24-12-2015 15 pounds

5, 13 pounds

] ;

Result:

LOAD *,

description as ReABC

Where description like '*pound*'

from

....

Re: Data transformation

ToniKautto — Thu, 01 Sep 2016 10:17:43 GMT

Not sure I understood you correctly. Would this be what you are looking for?

LOAD

id,

[voucher no],

If( description like '* pounds', 'ReABC', description) AS description

from

abc.qvd

Re: Data transformation

antoniotiman — Thu, 01 Sep 2016 10:31:26 GMT

Hi Tony,

If You need Value 'ReABC' in Field (not Field Name) You are correct.

However

Expression

Count(ReABC)

Count({<description={'ReABC'}>} description)

Regards,

Antonio

Re: Data transformation

Thu, 01 Sep 2016 11:30:36 GMT

table1:

load *

inline

[name

abc,

xyz,

12 pound,

23 pound,

34 pound]

;

table2:

load name as PoundName Resident table1

where name like '*pound*' ;

Re: Data transformation

0li5a3a — Thu, 01 Sep 2016 12:41:22 GMT

Hi ,

Is works just for half of my data, the problem is I got (20-04-16 14.99 voucher )this data I can't put it in my ReABC.

I tried something like : if(description like'*pound' or 'dd-mm*','REABC') AS Description but if I got something liken 20-04-16 14.99 is not going to ReABC.

One more think I suppose to see something like :

ReABC

AB20

BA20

Thanks

Re: Data transformation

0li5a3a — Thu, 01 Sep 2016 12:53:05 GMT

description contain something like below :

20-11-16 20 pound

20-12-15 14.15 voucher code

13 pounds voucher

RMab,

RMde

I want to have to the end something like this:

ReABC,

RMab,

RMde

Re: Data transformation

ToniKautto — Thu, 01 Sep 2016 21:44:54 GMT

How about using WildMatch() to look for any of multiple patterns?

LOAD
   If( WildMatch(description, '* pounds', '* voucher'), 'ReABC', description) AS description
Inline [
description
AB20
BA20,
20-11-16 20 pounds,
24-12-2015 15 pounds,
13 pounds
20-04-16 14.99 voucher 
];