topic Load Space Delimited TXT Data into QlikView in QlikView

Load Space Delimited TXT Data into QlikView

thedatabee — Sat, 27 Mar 2021 19:02:26 GMT

Hi.

I am trying to load a txt file containing weather data of the following format:

year city country avg_temp
1820 AL United States 9.86
1821 LA United States 12.36
1822 DE United States 18.03
1823 BR United States 20.36
1824 XY United States 21.65

While using LOAD, 'States' is being populated in the field avg_temp. I understand that it is considering 'United' as Country and 'states' as avg_temp. Is there any way to fix this?

Please advise.

Re: Load Space Delimited TXT Data into QlikView

Frank_Hartmann — Sat, 27 Mar 2021 21:15:30 GMT

Maybe like this:

LOAD left([@1:n],4) as year, right([@1:n],4) as avg_temp, subfield([@1:n],' ',2) as city, if(SubStringCount([@1:n],' ')>3,subfield([@1:n],' ',3)& ' '&subfield([@1:n],' ',4),subfield([@1:n],' ',3)) as country FROM [C:\Users\frank\Desktop\Neues Textdokument (3).txt] (fix, codepage is 28591, embedded labels);

Re: Load Space Delimited TXT Data into QlikView

rwunderlich — Sun, 28 Mar 2021 16:42:39 GMT

@Frank_Hartmann I like the approach of doing your own parsing using SubField(). I might modify it to take advantage of the fact that SubField can take a negative index, meaning counting from the right.

SubField([@1:n], ' ', -1) as avg_temp

-Rob