Solved: Where Clause With Excel Load Not Working As Expect... - Qlik Community

pnn44794 · ‎2017-03-18

Hello:

I'm attempting to load an Excel file where I have dates from 2016 and 2017 in the format of "M/D/YYYY hh:mm:ss AM" (or PM) or formatted "MM/DD/YYYY hh:mm:ss AM" (or PM), depending on the month and / or day. There's a total of 400 records. When I use the following snippet of code, it reduces the number of rows fetched to 289.

LOAD a,

b,

Date(Created, 'MM/DD/YYYY') as Created,

.

last-field;

FROM
xyz.xlsx
(ooxml, embedded labels, table is [Spend-Data])
Where [Created] > '12/31/2016';

Does any one have any ideas on why this isn't working? As always, thanks in advance and any and all help is appreciated.

swuehl · ‎2017-03-19

Maybe like

Where Year([Created]) > 2016;

View solution in original post

sasiparupudi1 · ‎2017-03-18

Try

Where Date(Created, 'MM/DD/YYYY')> '12/31/2016';

pnn44794 · ‎2017-03-18

Hello Sasidhar:

Thank you for your reply. No luck. This actually causes no rows to be returned.

Anil_Babu_Samineni · ‎2017-03-18

Seems you have extra Semicolon and then might your Date format is different in Where Condition. Check Accordingly. In fact your static date and Created field should same format in Where condition. Or else share sample with inline memory.

For me this example working. You may look the same

Sample:

LOAD Date(Date#(Created,'MM-DD-YYYY'),'DD/MM/YYYY') as Created, Welcome Inline [

Created, Welcome

12-21-2014, a

01-21-2016, b

01-01-2017, c

03-19-2017, d];

NoConcatenate

LOAD Created, Welcome Resident Sample Where Created >= '01/01/2017'; // Or MakeDate(YYYY, MM, DD)

DROP Table Sample;

Output seems

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

sasiparupudi1 · ‎2017-03-19

Try

Where Date(floor(timestamp#(Created, 'MM/DD/YYYY hh:mm:ss TT')),'DD/MM/YYYY')>'12/31/2016';

if this does not work, pl post sample excel records.

hth

sas

pnn44794 · ‎2017-03-19

Hello Sas:

Tried your Where clause above. It still didn't work. Here's 30 sample dates. I would expect to only load 4 records based on them being 2017 dates.

Created

1/11/2016 14:33

5/6/2016 8:15

5/16/2016 16:57

5/19/2016 9:14

5/23/2016 14:26

5/23/2016 14:52

5/26/2016 16:43

6/21/2016 9:50

7/7/2016 14:22

8/25/2016 10:34

9/8/2016 16:43

9/19/2016 14:24

9/20/2016 14:55

10/3/2016 16:51

12/1/2016 13:00

12/5/2016 11:05

12/19/2016 14:16

2/23/2017 8:46

2/27/2017 9:53

2/7/2017 14:27

2/4/2016 13:06

12/6/2016 16:48

9/19/2016 8:33

4/29/2016 12:56

1/8/2016 13:33

8/24/2016 12:54

2/2/2017 16:05

8/23/2016 15:08

1/20/2016 17:18

pnn44794 · ‎2017-03-19

Hello Anil:

No luck. Here's 30 sample dates of which I should only load 4 records.