Try specifying the quoting format, in the last line of your script:
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Besides, the date format should be 'M/DD/YYYY' instead.
Hope that helps.
testMSQ.qvw 146.8 K
first of all, thanks for your answers.
After posting the original message, I've been trying to use PurgeChar functions and the I've found your suggestion Kaushik.
Using your advice I've get closer to the correct interpretation of the file but only part of the dates are recognized as dates, the rest are recognized as text chains.
I got closer using this instead:
date(num(floor(Fecha))) as Fecha
Using this statement I got every date from the file in date data type.
Also I tried using the quoting parameter in the script statement as you said Miguel, but I haven't get anything as I would like.
Thanks for the file, but as far as I'm using the personal edition, I'm not able to open it.
I'm attaching my original file too, cuase it has more text format possibilities.
My actual script looks like:
SET MoneyFormat='#,##0.00 €;-#,##0.00 €';
SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff]';
LOAD date(num(floor(PurgeChar(Fecha,'"')))) as Fecha,
PurgeChar(Cliente, '"') as Cliente,
Num(PurgeChar(Ventas,'"'),'#,###','.',',') as Ventas
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
Thanks again for your answers.
Kind regards, Aitor.
P.S.: The original file is in Spanish so if neccessary, the translation would be:
Fecha = Date
Cliente = Client
Ventas = Sales
Fecha1.csv 7.3 K
That is a kind of a nightmare file, and it requires a lot of string formatting, so my first piece of advice is "get a cleaner file if possible". Anyway, the following will do
CleanData: LOAD Date(Date#(PurgeChar(SubField(@1:n, ',', 1), '"'), 'M/D/YYYY')) AS Fecha, Replace(SubField(PurgeChar(Replace(@1:n, ', ', '$$$'), '""'), ',', 2), '$$$', ', ') AS Cliente, Num(SubField(PurgeChar(Replace(@1:n, ', ', '$$$'), '""'), ',', 3) & SubField(PurgeChar(Replace(@1:n, ', ', '$$$'), '""'), ',', 4)) AS Ventas FROM [Fecha1.csv] (fix, codepage is 1252);
As you can see, there is a lot of string functions. If your file is some hundreds of thousands rows worth of data it will take real long to load, and probably you will need to clean up the file before loading it into QlikView.
But basically, what the above does is to use each line as a whole (@1:n) remove the double quotes and use each of the comma separated resulting values as fields, except for the Ventas field, where it concatenates the third and the fourth value, since "1,200" should read 1200. I replace as well the "" for $$$ (or any other non existent value) because I know the Cliente field has some values with commas within, that I then restore to " ," because I want to keep them.
It's a good example on how to struggle with crazy formatted sources using string functions, and I do not discard that may exist some other cleaner and easier ways to get the same results, irrespective the regional settings. Is it worth the work in QlikView instead of using some other tools to clean up data? That's up to you. I'd say that, in most cases, it's not worth the time you spend on doing this, but anyway.
Hope that helps.
qcomLoadQuotedFile.qvw 151.8 K
thanks for your answer. It really works.
The code has so many things that I need to learn and I do agree that's a good way to struggle yourself with nightmare source files. If you get success you finally learn a bit more.
Yesterday while I was reading the qlikview 11 reference manual I found "Alt" function and I thought that it could be a good option for this file. I'll work that way after studying carefully you answer, and trying to understand every step.
About the deeper discussion, as I told at first, I'm knew on this. I have no idea about how much time the performance use to take with data. I'm just following a tutorial example so dataset it's short and the performance will not get worse beacause the cleaning transformations. In my future project, I'll see how the situation looks, and taking in mind your words, I'll probably have to clean my Perl books and prepare a pre-treatment script to format the file properly.
Once again, thanks for answering!!