Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can anyone help me how to split the address to below columns?
Add1
Street,
City
County
Country
Postcode
Thanks.
Please see attached.
This is a typical delimited file. use the wizard to open it in QV, it will automatically see the fields. The only problem is if comma is also used in the text fields, which for your sake, I hope not.
Thanks. I just attached sample. But actually the data is coming from SQL table. will it work?
You might need to pad your data to ensure you've got the right data in the right field. I'm also hoping you just typed that text in as an example, not an actual extract of data!
no, it will not.
is the data coming as one single string? So each line in the text file is actually one field value?
Your attached sample is tab delimited file when you read this file in qlikview then you get separate columns not need to separate the columns. But check data is correct in the file then you get correct columns.
Hope this helps
If data is in single column then use Subfield function to extract the address like
=SubField(Add,1)
=SubField(Add,2)
Hope this helps
If these comma separated values are stored in a single SQL field, you could use SUBFIELD() to separate them. For example:
LOAD
SUBFIELD([FieldName],',',1) AS ADD1,
SUBFIELD([FieldName],',',2) AS Street,
SUBFIELD([FieldName],',',3) AS City,
SUBFIELD([FieldName],',',4) AS County,
SUBFIELD([FieldName],',',5) AS Country,
SUBFIELD([FieldName],',',6) AS Postcode;
SQL SELECT
[Fieldname]
FROM TABLE;
That's how I have data. Is there any other solution? Please help me