topic Re: Add and remove Fields inside column in QlikView

Add and remove Fields inside column

nareshthavidishetty — Tue, 27 Oct 2015 07:16:25 GMT

Hi I have an issue like,

Store Sales

A 100

B 200

C 300

D 400

Her I need add two rows like (A+B) as B and then I need remove ‘B’.

Can anyone help me how to do this in detail.

Re: Add and remove Fields inside column

rubenmarin — Tue, 27 Oct 2015 08:00:21 GMT

Hi naresh, you can use a precedent LOAD to change the original data:

Data:

LOAD A,

A + B as A+B,

LOAD A,B,C,D From ...;

This ends with a table named Data and fields A, A+B, C and D

Re: Add and remove Fields inside column

nareshthavidishetty — Tue, 27 Oct 2015 08:07:09 GMT

Thanks for your quick reply,

Here my columns are Store and Sales,in a store column i have a records like(A,B,C,D).

My requirement is ,i want to sum the A and B record after that i want to remove A record in Store Column.

Re: Add and remove Fields inside column

maxgro — Tue, 27 Oct 2015 08:07:48 GMT

in script you can

source:

load * inline [

Store Sales

A 100

B 200

C 300

D 400

] (delimiter is spaces);

load

'A+B' as Store,

sum(Sales) as Sales

Resident source

where Match(Store, 'A', 'B');

load * Resident source where Store = 'A+B';

final: load *, rowno() as id Resident source where Store <> 'B';

DROP Table source;

Re: Add and remove Fields inside column

nareshthavidishetty — Tue, 27 Oct 2015 08:09:22 GMT

O/P is like:Store Sales

A 300

C 300

D 400

Re: Add and remove Fields inside column

nareshthavidishetty — Tue, 27 Oct 2015 08:21:17 GMT

Thanks Maxgro it's working fine, but I want to rename A+B as B.

Re: Add and remove Fields inside column

jagan — Tue, 27 Oct 2015 08:22:53 GMT

Hi Naresh,

Try like this

Data:

LOAD

If(Match(Store, 'A', 'B'), 'A', Store) AS Store

Sales

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Add and remove Fields inside column

maxgro — Tue, 27 Oct 2015 08:26:37 GMT

change the final load

final:

load if(Store='A+B','B',Store) as Store, Sales, rowno() as id Resident source where Store <> 'B';

Re: Add and remove Fields inside column

nareshthavidishetty — Tue, 27 Oct 2015 09:11:00 GMT

I need to add A+B and then remove B

Re: Add and remove Fields inside column

jagan — Tue, 27 Oct 2015 09:37:15 GMT

Hi Naresh,

Try this script

Data:

LOAD

If(Match(Store, 'A', 'B'), 'A', Store) AS Store,

Sales

INLINE [

Store, Sales

A, 100

B, 200

C, 300

D, 400];

Hope this helps you. Using above script B is changed to A.

Regards,

Jagan.