How can I read my data correctly?

mlarruda — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How can I read my data correctly?

adityaakshaya — Tue, 08 May 2018 19:02:15 GMT

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.

Re: How can I read my data correctly?

mlarruda — Tue, 08 May 2018 19:07:20 GMT

Thank you. But what about the Date fields?

Re: How can I read my data correctly?

adityaakshaya — Tue, 08 May 2018 19:13:34 GMT

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

Re: How can I read my data correctly?

mlarruda — Tue, 08 May 2018 19:28:43 GMT

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

Re: How can I read my data correctly?

vishsaggi — Tue, 08 May 2018 19:40:11 GMT

read this:

date(date#()) function

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

date() is a formatting function to display.

Re: How can I read my data correctly?

mlarruda — Tue, 08 May 2018 20:22:28 GMT

Thanks! Worked!

Re: How can I read my data correctly?

mlarruda — Tue, 08 May 2018 20:22:37 GMT

Thanks! Worked!

topic Re: How can I read my data correctly? in QlikView

How can I read my data correctly?

Re: How can I read my data correctly?

Re: How can I read my data correctly?

Re: How can I read my data correctly?

Re: How can I read my data correctly?

Re: How can I read my data correctly?

Re: How can I read my data correctly?

Re: How can I read my data correctly?