Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a .out file with 20 columns and this file is space delimited. When I take it in Qlikview any column value which has space is moved to two different columns. Could you please suggest how I can fix this. Thanks
I am attaching an example of a .txt file and corresponding qvw file. As you see name is Ab John which is split in two columns, I want it in same column.
Thanks
Message was edited by: Priyadarsini Krishnamurthy Added the attachment.
So actually your input file has a fixed width format? there is an import file type for that.
Could post a sample?
Hi
Directory;
LOAD col_1,
col_2,
col_3,
col_4,
col_5,
col_6,
col_7,
col_8,
col_9,
col_10,
col_11,
col_12,
col_13,
col_14,
col_15,
col_16,
col_17,
col_18,
col_19,
col_20
FROM
[..\..\..\..\..\..\Desktop\web_Qlik\dat.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
if there is no fixed length, spaces as delimiter, column values containing spaces and no quotation then it's going to be difficult.
Please provide some sample data to clarify.
thanks
regards
Marco
Hi,
I have attached a sample please
Thanks
hi,
LOAD Name,
Age,
Addr1,
Addr2
FROM
[..\..\..\..\..\Desktop\web_Qlik\sample.txt]
(txt, codepage is 1252, embedded labels, delimiter is spaces, msq);
For the Provided sample, the below script works (Using Fixed Record in transform wizard)
T1:
LOAD @1:7,
@8:12,
@13:24,
@25:n
FROM
sample.txt
(fix, codepage is 1252, embedded labels);
This is going to be a little complicated as mentioned above. I would start by loading in all possible fields (as separated by spaces) so in your sample file I would load in 7 columns.
Easy option. If you always know that Name will have 1 space and each address will have 1 space each you can just concat the two fields together.
LOAD @1&' '&@2,
@3,
@4&' '&@5,
@6&' '&@7,
FROM
(txt, codepage is 1252, no labels, delimiter is spaces, msq);
If you don't know that there will always be 1 space in those columns you are going to need a lot more complicated code evaluating what is in each field. for example you know that Age is a number you can evaluate all of the fields for the first Numeric field and make everything before that the name. This is by no means easy and some situations (addr1 followed by addr2) are near impossible to get perfect but it should give you some direction.
do you have any control over the output file?
I didn't see it was fixed with. I noticed Ab John had one space and BD Rob had 2 but didn't put that together. The fixed with solution would make things easier if that holds up.