topic Re: How to create field with date in QlikView

How to create field with date

Sat, 05 Oct 2013 17:39:20 GMT

Hi,I have 3 fields with Day, Month and Year and would like to get one Date field. Can somebody help me with that?

Later, I would like to use this field in criteria where I want to show only those records where the date is older than the current date. Any clues where I can find my solution?

Thanks

Re: How to create field with date

swuehl — Sat, 05 Oct 2013 17:48:21 GMT

Try

MakeDate(YearField, MonthField, DayField) as DateField

to create a date field from your three fields.

Regarding your second question, where do you want to use this filter? In the script ( --> look into a WHERE clause) or within a chart (for example, expression could look like sum(if(DateField < today(), Amount)) )

Re: How to create field with date

Sat, 05 Oct 2013 17:55:52 GMT

Thanks. I dont know the answer, I have to study it little bit more. I want to show only records of invoices which are overdue and want also to add another field with number of overdue days. If I understand it correct, I have more options, how to do this.

Re: How to create field with date

Tue, 08 Oct 2013 14:43:08 GMT

We tried to create a date field from our three fields

MakeDate[[C068_RokSplatnosti],[C067_MesSplatnosti],[C066_DenSplatnosti]] as `DateField`

but we found a problem

Syntax error (missing operator) in query expression 'MakeDate[[C068_RokSplatnosti]'.

Do you have any advice ?

Re: How to create field with date

Tue, 08 Oct 2013 14:46:22 GMT

Use the following;

MakeDate([C068_RokSplatnosti],[C067_MesSplatnosti],[C066_DenSplatnosti]) as `DateField`

You were just using the wrong kind of brackets to define the Makedate.

Steve

Re: How to create field with date

israrkhan — Tue, 08 Oct 2013 14:46:30 GMT

try below , ( ) was missing

MakeDate([C068_RokSplatnosti],[C067_MesSplatnosti],[C066_DenSplatnosti]) as DateField

Re: How to create field with date

Tue, 08 Oct 2013 15:13:11 GMT

I tried that

MakeDate([C068_RokSplatnosti],[C067_MesSplatnosti],[C066_DenSplatnosti]) as `DateField`

but I found another problem

SQL##f - SqlState: 37000, ErrorCode: 4294964194, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'MakeDate' in expression.

Re: How to create field with date

israrkhan — Tue, 08 Oct 2013 15:14:55 GMT

can you post your script here... not sure where you are using this funcion.

Re: How to create field with date

swuehl — Tue, 08 Oct 2013 15:17:22 GMT

You need to use QV functions in the LOAD part, not the SQL part (which is just sent to the DB driver for parsing):

LOAD

MakeDate([C068_RokSplatnosti],[C067_MesSplatnosti],[C066_DenSplatnosti]) as DateField;

SQL SELECT

[C068_RokSplatnosti],

[C067_MesSplatnosti],

[C066_DenSplatnosti]

FROM ...;

or use an appropriate SQL function in the SQL part instead.

Re: How to create field with date

Tue, 08 Oct 2013 15:32:54 GMT

Load

makedate(day field,month field,year field) as date;

sql select *

from database table name;

Load

resident A where date > today();

drop A;

Re: How to create field with date

Wed, 09 Oct 2013 09:20:52 GMT

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='D. M. YYYY';

SET TimestampFormat='D. M. YYYY h:mm:ss[.fff]';

SET MonthNames='1;2;3;4;5;6;7;8;9;10;11;12';

SET DayNames='po;ut;st;št;pi;so;ne';

ODBC CONNECT32 TO [MS Access Database;DBQ=D:\Ocelik\ROK_2013.mdb];

SQL SELECT *

FROM MSysAccessStorage;

LOAD

MakeDate([C068_RokSplatnosti],[C067_MesSplatnosti],[C066_DenSplatnosti]) as `DateField`;

SQL SELECT `C000_ID`,

`C001_Vymazany`,

`C002_Oznaceny`,

`C003_Zamknuty`,

`C004_Systemovy`, .

`C254_PohladavkaZavazok`,

[C228_UhradeneTM]*[C221_SumaSpoluNizsia] as `Received Value`

FROM `T040_EUD`;

I have posted my script. I can´t find my new field in sheet propertis in available fields and I can´t find my new field in table box properties in available fields.

Re: How to create field with date

CELAMBARASAN — Wed, 09 Oct 2013 09:31:35 GMT

LOAD *,

MakeDate([C068_RokSplatnosti],[C067_MesSplatnosti],[C066_DenSplatnosti]) as DateField

Where MakeDate([C068_RokSplatnosti],[C067_MesSplatnosti],[C066_DenSplatnosti]) <= Today();//To get date before the current date

SQL SELECT `C000_ID`,

`C001_Vymazany`,

`C002_Oznaceny`,

`C003_Zamknuty`,

`C004_Systemovy`, .

`C254_PohladavkaZavazok`,

[C228_UhradeneTM]*[C221_SumaSpoluNizsia] as `Received Value`

FROM `T040_EUD`;

Re: How to create field with date

preminqlik — Wed, 09 Oct 2013 09:33:17 GMT

table1:

SQL SELECT `C000_ID`,

`C001_Vymazany`,

`C002_Oznaceny`,

`C003_Zamknuty`,

`C004_Systemovy`, .

`C254_PohladavkaZavazok`,

[C228_UhradeneTM]*[C221_SumaSpoluNizsia] as `Received Value`

FROM `T040_EUD`;

table2:

noconcatenate

load *,MakeDate([C068_RokSplatnosti],[C067_MesSplatnosti],[C066_DenSplatnosti]) as [Date Field]

resident table1;

drop table table1;

Re: How to create field with date

Wed, 09 Oct 2013 11:44:26 GMT

Yep Thanks but I need in date field all dates , not only To get date before the current date.

Re: How to create field with date

CELAMBARASAN — Wed, 09 Oct 2013 11:52:35 GMT

Remove that where condition

LOAD *,

MakeDate([C068_RokSplatnosti],[C067_MesSplatnosti],[C066_DenSplatnosti]) as DateField;

SQL SELECT `C000_ID`,

`C001_Vymazany`,

`C002_Oznaceny`,

`C003_Zamknuty`,

`C004_Systemovy`, .

`C254_PohladavkaZavazok`,

[C228_UhradeneTM]*[C221_SumaSpoluNizsia] as `Received Value`

FROM `T040_EUD`;