Where statement to omit a certain type of data

[stuwannop]

Hi Everyone

I am pulling data from a Dynamics Nav GL table based on the posting date. In my script I want to exclude any dates that are prefixed with a C. For example C31/03/2007, C31/03/2007, C31/03/2010 etc.etc. The dates are aleways on the 31st March (but can be any year) and I only want to exclude the data prefixed with a C.

Thanks for any help

Stu

[jagan]

Hi,

Try this

WHERE Left(DateDimension, 1) <> 'C'

Hope this helps you.

Regards,

Jagan.

[stuwannop]

Thanks Jagan, that works fine, although that has given rise to another question if you can help.

I thought my raw data would have been prefixed with a C, it turns out it wasn't. The data I want to exclude now I have just found out is anything with a time <> 23:59.

So in other words, exclude "31/03/2006 23:59" but include "31/03/2006 00:00". All times in the data are either 23:59 or 00:00 and it's the 23:59 timestamps I want to exclude.

Sorry for changing the goalposts!

Thanks

Stu

[jagan]

Hi,

Try like this

WHERE Left(DateDimension, 1) <> 'C' OR Right(DateDimension, 5) <> '23:59'

Hope this helps you.

Regards,

Jagan.

[stuwannop]

Thanks Jagan. I understand the formula fine - I also put it into a text box to check it was displaying the correct values:  =right([Posting Date],5) and it was OK. However, when I incorperate it into the script using a WHERE clause it still brings all the data through, the script is below. Could it be something to do with working with time sensitive data perhaps? 

SQL

SELECT *

FROM "DynamicsNAV".dbo."AGGREGATES$G_L Entry"

WHERE Right([Posting Date], 5) <> '59:59' ;

[jagan · Accepted solution]

HI,

Left and Right() are qlikview functions, it should not be used with SQL Queries.  Try like this

TableName:

Load

*

WHERE Right([Posting Date], 5) <> '59:59;

SQL SELECT *

FROM "DynamicsNAV".dbo."AGGREGATES$G_L Entry"

WHERE Right([Posting Date], 5) <> '59:59;

Hope this helps you.

Regards,

Jagan.

[stuwannop]

Thanks Jagan.

[#8]

Hi jagan,

I have Table

Month Sales

1,2500

3,4000

5,6000

7,5000

8,3500

Can I do the sum of the sales for the specific months using sql statement, please say me how it can be written. is it possible to write only in the scripting or in textboxes,charts also can we write?

thanks in advance

regards

ramesh    

[jagan]

Hi,

Can you close this discussion by clicking correct answer to my latest post, so that it would be helpful for others to find the solution for similar requirement.

Regards,

Jagan.

[jagan]

Hi Ramesh,

I didn't get you, what you are asking.  But I think you are asking about to show Sum of only particular months.

You can do like this

=Sum({<Month={1,5, 9}>} Sales)

The above expression sums up the sales for months 1, 5 and 9.

If you want to restrict data in script for this months you can do this

Select *from tableName where Month(Date) in (1,5,9)

Hope this helps you.  If this is not you are expecting can you come up with an example.

Regards,

Jagan.