Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
May be this?
SubField(DESIGN_NO, '-', 1) as DESIGN_NO
And now use dimension as DESIGN_NO
Expression Sum(Sales QTY)
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)
Hii,
you can use
Purgechar(DESIGN_NO,'-') as DESIGN_NO
if you want to remove only - from the DESIGN_NO
hello Vivek,
try this ,
KeepChar(DESIGN_NO,' 0123456789') as DESIGN_NO
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.
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 ';'
Try this
Purgechar(UPPER(DESIGN_NO),'ABCDEFGHIJKLMNOPQRSTUVWXYZ-')
hope this helps
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..
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