Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
KirstenKa
Creator II
Creator II

Change format data value in load script

I have this value in my dataset 

SAP.Trading Co
1011B034-003

 

And would like to transform this value in my load script to this value 

1011B034.003
Labels (1)
2 Solutions

Accepted Solutions
BrunPierre
Partner - Master II
Partner - Master II

Hi, if it's just to substitute hyphens (-) with decimals (.), then you should make use of the Replace ().

Replace("SAP.Trading Co", '-', '.')  as "SAP.Trading Co"

View solution in original post

KirstenKa
Creator II
Creator II
Author

Thanks I placed the join after the load, that worked

View solution in original post

4 Replies
BrunPierre
Partner - Master II
Partner - Master II

Hi, if it's just to substitute hyphens (-) with decimals (.), then you should make use of the Replace ().

Replace("SAP.Trading Co", '-', '.')  as "SAP.Trading Co"

KirstenKa
Creator II
Creator II
Author

Thanks @BrunPierre  that seems to work, though I also would like to join on this new value, but since the new value is added after the join it doesn't recognize the value

The join looks like this

C8:
LOAD
"Item Trading Code" & '|' & "Color Code" & '|' & "sku" as %ItemColor_GlobalId,
"Item Trading Code" & '|' & "Color Code"& '|' & "sku" as ItemColor_GlobalId,

Left Join( C8 )
SAP:

LOAD
"Item ID" & '|' & "Color" & '|' & "SAP.Trading Co1" as %ItemColor_GlobalId,
"Item ID" & '|' & "Color" & '|' & "SAP.Trading Co1" as ItemColor_GlobalId_SAP,
Article,
Replace("Trading Co", '-', '.') as "SAP.Trading Co1",

BrunPierre
Partner - Master II
Partner - Master II

If my understanding is correct, then this way;

C8:
LOAD
"Item Trading Code" & '|' & "Color Code" & '|' & "sku" as %ItemColor_GlobalId,
"Item Trading Code" & '|' & "Color Code"& '|' & "sku" as ItemColor_GlobalId,

Left Join(C8)
LOAD *,
"Item ID" & '|' & "Color" & '|' & "SAP.Trading Co1" as %ItemColor_GlobalId,
"Item ID" & '|' & "Color" & '|' & "SAP.Trading Co1" as ItemColor_GlobalId_SAP

SAP:
LOAD Article,
Replace("Trading Co", '-', '.') as "SAP.Trading Co1",
...

KirstenKa
Creator II
Creator II
Author

Thanks I placed the join after the load, that worked