Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

The date problem--multiple same date,creating an extra fields after loading the data

Hi, I attached the data set

1. try to create a filds buy using 'quantity * unitPrice' for analytics after loading the data. Not sure how to do that

2. the original formate for Invoice date is 'date and time', then I use EXCEL change it to time

then when I try to use 'Invoicedate' as filter, it showing me multiple records for same date, e.g. 2010-12-01  but I only want 1, anyone can help me with that?

trying to use Date as filter to analysis the revenue= quantity * unitPrice for each country and rank it from High to low.

BTW: any one have good suggestion regarding recover Qlik capability. Haven't use it for analysis for 2 months now, Feel like I am forgetting basic . It is terrible !

Thank you!

Regards,

Celine

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Celine,

Your getting duplicated dates values because as the date has a timestamp component such as:

"01/01/2017 10:10:10"

"01/01/2017 10:11:10"

"01/01/2017 10:12:10"

and when you load this, it will give you multiple lines because of the decimal aspect of the date (for the first line, the corresponding number will be 42736,42 if you input the date and convert to number in Excel)


Use the following to correct that on your load statement as it will round the number that represents the date:


LOAD InvoiceNo,

     StockCode,

     Description,

     Quantity,

    floor(InvoiceDate) as InvoiceDate,

     UnitPrice,

     CustomerID,

     Country

FROM

(ooxml, embedded labels, table is [Online Retail]);

View solution in original post

3 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Celine,

Your getting duplicated dates values because as the date has a timestamp component such as:

"01/01/2017 10:10:10"

"01/01/2017 10:11:10"

"01/01/2017 10:12:10"

and when you load this, it will give you multiple lines because of the decimal aspect of the date (for the first line, the corresponding number will be 42736,42 if you input the date and convert to number in Excel)


Use the following to correct that on your load statement as it will round the number that represents the date:


LOAD InvoiceNo,

     StockCode,

     Description,

     Quantity,

    floor(InvoiceDate) as InvoiceDate,

     UnitPrice,

     CustomerID,

     Country

FROM

(ooxml, embedded labels, table is [Online Retail]);

Not applicable
Author

And also, any chance then, only extract year and month from the date (not only month) for future monthly analysis?

Thank you!

felipedl
Partner - Specialist III
Partner - Specialist III

Something like this Celine:

data:

LOAD InvoiceNo,

     StockCode,

     Description,

     Quantity,

     date(floor(InvoiceDate),'DD-MM-YYYY') as InvoiceDate,

     Month(InvoiceDate) & '-' & Year(InvoiceDate) as [Month-Year],

     UnitPrice,

     CustomerID,

     Country,

     Quantity * UnitPrice as Revenue

FROM

(ooxml, embedded labels, table is [Online Retail]);