Modify aspect of datas loaded (put dash on a number reference)

[#1]

Hi everyone,

I am now facing a new problem I can't figure out : for a same project, depending from where I load the information I need, I will have my sales reference with two different aspects. To give an exemple :

- in the CRM, the sale is SI-15-1010A

- in the ERP, the sale is SI151010A

When the company was using Excel to make analysis, they were able to connect those 2 sales reference through a formula (adding or erasing the dash)

So as this code is the key to connect all the information available about each sale and project, I am looking for a way to be able to load it with the same aspect into Qlik Sense through my data model.

Is it possible to create a dimension adding or erasing those dashes?

Thank you in advance!

[datanibbler]

Hi Alexia,

are the dashes always in the same places?

If so, try

>> LEFT([field], 2) & '-' & MID([field], 3, 2) & '-' & RIGHT([field], 5) <<

That should work as a > calculated dimension < as well.

HTH

Best regards,

DataNibbler

[Ralf-Narfeldt]

Easiest is to erase the dashes in the CRM data with the expression:

PurgeChar ( SalesRefField, '-' )

[sasiparupudi1 · Accepted solution]

I think it is better to remove the - than adding them which is tricky based on the string lengths so I recommend using purgecar on the string with -

to get SI151010A, use purgecar

PurgeChar('SI-15-1010A','-')

if you have fixed length,use

mid(ERP,1,2)&'-'&mid(ERP,3,2)&'-'&mid(ERP,5,len(ERP))

where

ERP=SI151010A

hth

Sasi

[#5]

Thank you all very much! It works perfectly now!

[Ralf-Narfeldt]

Please remember to set answers to Helpful and Correct, so this question will be shown as Answered!