Forum Discussion
Leading Zero
If your source data has leading zeros, then you need to solve this when you import the data (assuming you are not connecting directly to the data source), by making sure you format the column as text in the query editor. Otherwise, you will need to create a calculated column that pads the store IDs with the correct number of leading zeros.
If your source data has leading zeros, then you need to solve this when you import the data (assuming you are not connecting directly to the data source), by making sure you format the column as text in the query editor. Otherwise, you will need to create a calculated column that pads the store IDs with the correct number of leading zeros.
I tried this solution but it gave me an error. I believe my problem is that the column I want to retain the leading zeroes on is the result of Max(column_name) . My table is grouped by booking_ID, getting max(column), and trying to use the result to merge booking_ID with max(seq) as a key to another table.
So my problem is without the leading zeroes, a one to one is turning into a many to many
12864 13
128641 3 are both coming out 1286413 creating many to many for the join.
When I do a format( max(seq), '000') I get an error.
Also changing the format to text doesn't add the leading zeroes.
thanks
Hi Maryann,
Tried a similar piece and have no issues but not sure what you are doing wrong.
The formula looks to be missing [] and the location it is pulling data from but not sure.
What you could try to make them unique is an "&" statement.
column 1 &" - "&max(Seq)
column 1 = column containing 12864 and 128641
This should work unless the error is in your: max(seq).
Hope this makes sense.
SJ
Thanks SJ , This looks like a good fix .
