Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

how to make selections on dates in SQL when coded as numbers

HI everybody,

I'm trying, qithout success, to achieve the following.

Usually, I'm solving this kind of issue by means of an ApplyingMap, but I'm not sure it is the optimal solution.

I have a field which is including all the incoming quantities.

I then would like to include a field which is considering just the quantities that are incoming in the next two weeks AND that are coming by truck, regardless if within two weeks or one month time. All these quantities need to be in the same field.

The source is AS400 and the dates are coded as 20121210.

I think that the SQL date-related functions are not working properly because those 'dates' are coded as numbers (I am getting no results).

How would you tackle this problem? Importing the AS400 source in QV first, by using a Date#, and then doing some IF functions regarding the dates needed and whether shipment is by truck?

thank you!

5 Replies
Not applicable

Re: how to make selections on dates in SQL when coded as numbers

could you paste your script ?

have you tried using

date#(YourDate,'YYYYMMDD')

function?

Not applicable

Re: how to make selections on dates in SQL when coded as numbers

thank you for your answer.

I cannot use the date#(date'YYYYMMDD') function, or better, I can use it, but is not effective.

By using that function, I would fist need to load all the data, interpret the dates, so that then I can filter on the two weeks.

My goal is to have the filtering directly from the source, so, in the SQL command, to have a WHERE statement, which is picking up just the dates of my interests. but yeah, problem is that those datesin SQL are numbers.

I was trying some SQL command like ADD date or so (did not save them), but they were not leading to anywhere.

So in the end, to save time (in design phase, for sure not in reload timings) I had to rely on the "easy" way, i.e. importing and then filtering from the RESIDENT table.

but if a proper way of filtering the weeks directly from SQL source, where the field is a number, woudl be much appreciated, tho I'm aware the script might result quite complex, so maybe all in all is not worth the trouble...

thank you!

Not applicable

Re: how to make selections on dates in SQL when coded as numbers

1. could you paste part of script (after where clause)?

2. you want to make two variables in script (start and end date) and load only dates from that period of time, am i right ?

pgrenier
Contributor III

Re: how to make selections on dates in SQL when coded as numbers

Good day,

You may try creating two variables, the first for your earliest date, and a second for the latest date, then refer to those variables directly in your SELECT statement, as such:

LET v_MinDate = '20130115';

LET v_MaxDate = '20130130';

TableName:

LOAD *;

SELECT *

FROM AS400_table_name

WHERE date_field >= $(v_MinDate) and date_file <= $(v_MaxDate);

Cheers,

Philippe

Employee
Employee

Re: how to make selections on dates in SQL when coded as numbers

You write about the Date#() function that

     "By using that function, I would fist need to load all the data, interpret the dates, so that then I can filter on the two weeks."

But this is not true. If you use Preceding Load, you can do it in one go:

Load * Where week(NewDate) = 2 ;

Load *,

     Date#(Date,'YYYYMMDD') as NewDate;

SQL SELECT ... FROM ;

HCI

Community Browser