Edit dimensions in Charts with multiple line of scripts.

[#1]

Hallo QV geniuses,

I have a very simple problem. But I don't know how to script it.

I have this sample data:

Telephone
057 982 12 12
123456789
056 678 2312
234567893
657345124
045 123 89 34

I want my final output for every data in the Telephone to be

Telephone
057 982 12 12
012 345 67 89
056 678 23 12
023 456 78 93
065 734 51 24
045 123 89 34

What I did was:

a.PurgeChar(Telephone, Chr(32))  = Because I want to purge all "spaces"

b. Repeat('0', 10- Len(Telephone) & Telephone = To add leading zeros.

c.Left(Telephone,3) & ' ' &mid(Telephone,4,3) & ' ' & mid(Telephone,7,2) & ' ' & mid(Telephone,9,2) = To Format it

As you can see I got what I want but the problem is I want to put this 3 functions in one single script.

I have a Straight table and in the dimension tab I select Telephone and EDIT. And in the edit script I want ALL THOSE 3 functions. How can I do this?

Thanks a lot.

[aveeeeeee7en]

Hi Reagan

Try something like this:

AAA:

LOAD Telephone

FROM

.....\Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

BBB:

Load

Telephone,

Trim(if(Left(Telephone,1)='0',Replace(Telephone,' ',''),'0'&Replace(Telephone,' ',''))) AS Telephone11

Resident AAA;

DROP Table AAA;

CCC:

Load

Telephone,

Telephone11,

Left(Telephone11,3) & ' ' &mid(Telephone11,4,3) & ' ' & mid(Telephone11,7,2) & ' ' & mid(Telephone11,9,2)  AS New_Telephone

Resident BBB;

DRop Table BBB;

See the Attachment.

[maxgro]

you can use a preceding load

load Left(Telephone,3) & ' ' &mid(Telephone,4,3) & ' ' & mid(Telephone,7,2) & ' ' & mid(Telephone,9,2) as Telephone;

load Repeat( '0', 10- Len(Telephone)) & Telephone as Telephone;

load PurgeChar(Telephone, Chr(32)) as Telephone;

load * Inline [

Telephone

057 982 12 12

123456789

056 678 2312

234567893

657345124

045 123 89 34

]

;

[#4]

Nag has given me an idea. Thank you so much. What I did was:

AAA:

Load

PurgeChar(Telephone, Chr(32)) as Telephone,

FROM

.....\Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

TelefonFormat:

Load

Telephone,

Repeat('0',10-Len(Telephone) & Telephone as TelephoneTemp,

Resident AAA;

Drop AAA;

And I made a calculated dimension in my table with this code:

Left(TelephoneTemp,3) & ' ' &mid(TelephoneTemp,4,3) & ' ' & mid(TelephoneTemp,7,2) & ' ' & mid(TelephoneTemp,9,2)

Thanks