topic Re: Remove Hyphen from the database in QlikView

Remove Hyphen from the database

Anonymous — Mon, 04 Sep 2017 09:20:06 GMT

Hello Everyone,

I have the data where it mention as, i want to prepare a pivot table where dimension will be my design no with out Hyphen.

DESIGN_NO	SALES QTY
1425895-EE	1
14532861-FD	2
58462879999-FF	5
1425895-EF	4
1425895-EG	8
1425895-DD	9
14532861-FF	10
58462879999-FA	1

How to get the data without hyphen,

After Removing of Hyphen,

Row Labels	Sum of SALES QTY
1425895	22
14532861	12
58462879999	6
Grand Total	40

Re: Remove Hyphen from the database

Anil_Babu_Samineni — Mon, 04 Sep 2017 22:15:28 GMT

May be this?

SubField(DESIGN_NO, '-', 1) as DESIGN_NO

And now use dimension as DESIGN_NO

Expression Sum(Sales QTY)

Re: Remove Hyphen from the database

Anonymous — Tue, 05 Sep 2017 04:28:42 GMT

Hi Anil,

Thanks for the reply.

Do i need to write this under Load editor script?

SubField(DESIGN_NO, '-', 1) as DESIGN_NO

And now use dimension as DESIGN_NO

Expression Sum(Sales QTY)

Re: Remove Hyphen from the database

divya_shinde — Tue, 05 Sep 2017 04:35:36 GMT

Hii,

you can use

Purgechar(DESIGN_NO,'-') as DESIGN_NO

if you want to remove only - from the DESIGN_NO

Re: Remove Hyphen from the database

rohitraut — Tue, 05 Sep 2017 04:54:13 GMT

hello Vivek,

try this ,

KeepChar(DESIGN_NO,' 0123456789') as DESIGN_NO

Re: Remove Hyphen from the database

Bala_s — Tue, 05 Sep 2017 05:29:00 GMT

Hi Vivek,

Use below code in script:

SubField(DESIGN_NO, '-', 1) as DESIGN_NO, after use DESIGN_NO as Dimension, you will get the result.

Re: Remove Hyphen from the database

avinashelite — Tue, 05 Sep 2017 05:35:08 GMT

Use

subfield(s, 'delimiter' [ , index ] )

In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. index is an optional integer denoting which of the substrings should be returned. If index is omitted when subfield is used in a field expression in a load statement, the subfield function will cause the load statement to automatically generate one full record of input data for each substring that can be found in s.

In its two-parameter version, the subfield function generates one record for each substring that can be taken from a larger string s with the delimiter 'delimiter'. If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated.

Examples:

(For three parameters)

subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'

subfield(S, ';' ,1) returns NULL if S is an empty string

subfield(S, ';' ,1) returns an empty string if S is ';'

Re: Remove Hyphen from the database

Tue, 05 Sep 2017 05:45:33 GMT

Try this

Purgechar(UPPER(DESIGN_NO),'ABCDEFGHIJKLMNOPQRSTUVWXYZ-')

hope this helps

Re: Remove Hyphen from the database

Anil_Babu_Samineni — Tue, 05 Sep 2017 06:12:56 GMT

I don't recommend to use purgechar over here because we don't know real data. So then, what if they have special characters in the first field..

Re: Remove Hyphen from the database

arvind_patil — Tue, 05 Sep 2017 06:26:38 GMT

Hi Vivek,

Please find below:

Data:

LOAD * Inline [

DESIGN_NO,SALESQTY

1425895-EE ,1

14532861-FD,2

58462879999-FF,5

1425895-EF,4

1425895-EG,8

1425895-DD, 9

14532861-FF ,10

58462879999-FA,1

];

NoConcatenate

Fi:

LOAD KeepChar(DESIGN_NO,'1234567890') as [DESIGN NO],

SALESQTY as [SALES QTY]

Resident Data;

Drop Table Data;

Thanks,

Arvind Patil

Re: Remove Hyphen from the database

avinashelite — Tue, 05 Sep 2017 07:22:58 GMT

You could use it in both the front end and script level ...Script end is the most preferred one

Re: Remove Hyphen from the database

its_anandrjs — Tue, 05 Sep 2017 07:33:48 GMT

You can try this

On UI you can do

Dimension :- Subfield( DESIGN_NO,'-',1 )

Expression:- Sum( [SALES QTY] )

On Load Script

Load

Subfield( DESIGN_NO,'-',1 ) as DESIGN_NO,

[SALES QTY]

From Location;