Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
Easiest is to erase the dashes in the CRM data with the expression:
PurgeChar ( SalesRefField, '-' )
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
Thank you all very much! It works perfectly now!
Please remember to set answers to Helpful and Correct, so this question will be shown as Answered!