Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Well, my problem is a bit complex. So, let me explain what is happening.
I have one file (let's call it file2018.qvw) with a script like this:
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File_1.csv]
(txt, utf8, embedded labels, delimiter is ';');
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File_2.csv]
(txt, utf8, embedded labels, delimiter is ';');
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File3.qvd]
(qvd);
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File4.qvd]
(qvd);
Also, I have another file (let's call it file2017.qvw) with a script similar to this:
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File_5.qvd]
(qvd);
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File_6.qvd]
(qvd);
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File7.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File8.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File9.xlsx]
(ooxml, embedded labels, table is [SQL Results]);
Well, what I want to do now is a partial merge of these files, using this script:
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File_1.csv]
(txt, utf8, embedded labels, delimiter is ';');
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File3.qvd]
(qvd);
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File4.qvd]
(qvd);
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File_6.qvd]
(qvd);
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File7.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
Concatenate
LOAD Customer_Name as NAME,
Date_of_Buy as DATE,
Name_of_Store as PLACE,
Value_of_Buy as PRICE
FROM
[File9.xlsx]
(ooxml, embedded labels, table is [SQL Results]);
Both file2017.qvw and file2018.qvw are read without problems but, when I open this merged files, the DATE and PRICE fields from File7.csv are not correctly read, producing text strings rather than numeric values.
Anyone knows how can I fix it?
Important: File1.csv and File2.csv have no quotes (their typical row is John;22/01/2018;England;1000) while File7.csv and File8.csv have quotes (their typical row is "Paul";"15/04/2017";"USA";"574").
So, many thanks in advance for any help.
Hi Marcelo,
To fix this issue, you can use num#(Value_of_Price) everywhere in the script.
Regards,
Akshaya
PS -If you find it helpful or correct, Please mark it.
Hi Marcelo,
To fix this issue, you can use num#(Value_of_Price) everywhere in the script.
Regards,
Akshaya
PS -If you find it helpful or correct, Please mark it.
Thank you. But what about the Date fields?
Hi Marcelo,
You can use Date# and Date function to convert it into proper date.
Date(Date#(Date_of_Buy),'M/D/YYYY') as Date
Regards,
Akshaya
Thank you, but what's the meaning of the outer Date(? Why do not use only the inner Date#(?
read this:
date#() is an interpretation function. Like converting any strings to dates.
date() is a formatting function to display.
Thanks! Worked!
Thanks! Worked!