topic Re: Where Clause With Excel Load Not Working As Expected in QlikView

Where Clause With Excel Load Not Working As Expected

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

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,

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.

Re: Where Clause With Excel Load Not Working As Expected

sasiparupudi1 — Sat, 18 Mar 2017 23:01:42 GMT

Try

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

Re: Where Clause With Excel Load Not Working As Expected

pnn44794 — Sun, 19 Mar 2017 00:18:56 GMT

Hello Sasidhar:

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

Re: Where Clause With Excel Load Not Working As Expected

Anil_Babu_Samineni — Sun, 19 Mar 2017 03:03:25 GMT

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

Re: Where Clause With Excel Load Not Working As Expected

sasiparupudi1 — Sun, 19 Mar 2017 19:03:25 GMT

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

Re: Where Clause With Excel Load Not Working As Expected

pnn44794 — Sun, 19 Mar 2017 20:11:01 GMT

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

Re: Where Clause With Excel Load Not Working As Expected

pnn44794 — Sun, 19 Mar 2017 20:12:48 GMT

Hello Anil:

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

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

Re: Where Clause With Excel Load Not Working As Expected

pnn44794 — Sun, 19 Mar 2017 20:14:26 GMT

What's weird and I just noticed this. The seconds and AM or PM did not paste, though I definitely see them in the data.

Re: Where Clause With Excel Load Not Working As Expected

pnn44794 — Sun, 19 Mar 2017 20:14:36 GMT

What's weird and I just noticed this. The seconds and AM or PM did not paste, though I definitely see them in the data.

Re: Where Clause With Excel Load Not Working As Expected

swuehl — Sun, 19 Mar 2017 20:16:14 GMT

Maybe like

Where Year([Created]) > 2016;

Re: Where Clause With Excel Load Not Working As Expected

swuehl — Sun, 19 Mar 2017 20:17:57 GMT

And double check your Created field can be interpreted as timestamp or date:

Why don’t my dates work?

Get the Dates Right

Re: Where Clause With Excel Load Not Working As Expected

pnn44794 — Sun, 13 Oct 2019 18:43:54 GMT

Hello Sas:

Hope this helps.

Re: Where Clause With Excel Load Not Working As Expected

pnn44794 — Sun, 13 Oct 2019 18:44:28 GMT

Hello Anil:

Hope this helps.

Re: Where Clause With Excel Load Not Working As Expected

pnn44794 — Sun, 19 Mar 2017 20:40:46 GMT

Come on Stefan!!!! That would be too easy. LOL. I definitely was making it harder to do than it needed to be. A rookie problem I suppose. Your Where clause worked. Thank you very much. I appreciate the help.

Re: Where Clause With Excel Load Not Working As Expected

pnn44794 — Sun, 19 Mar 2017 20:41:18 GMT

And thank you for the links. I have bookmarked them. Thanks again for the help.