How can I read my data correctly?

[mlarruda]

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.

[adityaakshaya · Accepted solution]

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.

[mlarruda]

Thank you. But what about the Date fields?

[adityaakshaya]

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

[mlarruda]

Thank you, but what's the meaning of the outer Date(? Why do not use only the inner Date#(?

[vishsaggi]

read this:

date(date#()) function

date#() is an interpretation function. Like converting any strings to dates.

date() is a formatting function to display.

[mlarruda]

Thanks! Worked!

[mlarruda]

Thanks! Worked!