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

[#1]

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

[felipedl · Accepted solution]

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]);

[#3]

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

Thank you!

[felipedl]

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]);