topic Re: problems with query for datefield = 2015 in QlikView

problems with query for datefield = 2015

Fri, 22 May 2015 09:52:09 GMT

Hi there,

i am new to qv and i hope you can help me again.

I want to write in a SQL Table like:

write_table:

LOAD

Ratenschema,

lieferant as lieferant_1,

reason as reason_1,

AVG(Erlös / cost) as faktor1,

AVG((DB_neu + Erstattung) / cost) as faktor2,

Datum_Datenbank as datum_test

resident write_faktor

WHERE

YEAR(Datum_Datenbank) = 2015

GROUP BY

Ratenschema,

lieferant,

reason,

Datum_Datenbank;

For i= 0 to NoOfRows('write_table') -1

LET V_FIELD1 = peek('Ratenschema',$(i),'write_table');

LET V_FIELD2 = peek('lieferant_1',$(i),'write_table');

LET V_FIELD3 = peek('reason_1',$(i),'write_table');

LET V_FIELD4 = peek('faktor1',$(i),'write_table');

LET V_FIELD5 = peek('faktor2',$(i),'write_table');

LET V_FIELD6 = peek('datum_test',$(i),'write_table');

SQL INSERT INTO dba.faktor_ratenschema VALUES(NULL, '$(V_FIELD1)', '$(V_FIELD2)', '$(V_FIELD3)', replace('$(V_FIELD4)', ',' , '.') , replace('$(V_FIELD5)', ',' , '.') , '$(V_FIELD6)');

NEXT;

But the restriction for the year doesnt work!

Any idea how to input data from year 2015 into the database?

Thanks and regards

Sabrina

Re: problems with query for datefield = 2015

anbu1984 — Fri, 22 May 2015 09:59:13 GMT

Is Datum_Datenbank Date field?

Why don't you use Where clause directly on Sql table instead of Resident table?

Re: problems with query for datefield = 2015

Fri, 22 May 2015 10:06:49 GMT

This is the first part of my load script:

qvd_berechnungen:

LOAD * from C:\test\berechnungen.qvd (qvd);

INNER JOIN (qvd_berechnungen)

qvd:

LOAD * from C:\test\verkäufe_neu.qvd (qvd);

write_faktor:

LOAD *, [DB-II] as DB_neu

resident qvd_berechnungen;

drop table qvd_berechnungen;

You see, the datum_datenbank field comes from a qvd file and i can't identify the data type. 😞

Re: problems with query for datefield = 2015

PrashantSangle — Fri, 22 May 2015 10:07:06 GMT

Hi,

Datum_Datenbank is proper datefield???

What format it contains.

Try with

wildmatch(Year(Datum_Datenbank),'2015')

Regards

Re: problems with query for datefield = 2015

PrashantSangle — Fri, 22 May 2015 10:09:20 GMT

Hi,

After loading

datum_datenbank field gives you number or date???

Use Date#() to covert String to date

Regards

Re: problems with query for datefield = 2015

Fri, 22 May 2015 10:13:31 GMT

In the preview of the table structure, the colum for datum_datenbank has the format YYYY-MM-DD

So i think its a date field?

I had allready tried out the Date#() Function but this doesn't work, too.

Re: problems with query for datefield = 2015

Fri, 22 May 2015 10:24:39 GMT

Didn't work too 😞

Re: problems with query for datefield = 2015

PrashantSangle — Fri, 22 May 2015 10:26:44 GMT

Hi,

Try

Year(Date#(Datum_Datenbank,'YYYY-MM-DD'))=2015

or Left(Datum_Datenbank,4)=2015

subfield(Datum_Datenbank,'-',1)=2015

Regards

Re: problems with query for datefield = 2015

Fri, 22 May 2015 10:27:59 GMT

Re: problems with query for datefield = 2015

PrashantSangle — Fri, 22 May 2015 10:30:45 GMT

Hi,

Have you try this

Year(Date#(Datum_Datenbank,'YYYY-MM-DD'))=2015

Left(Datum_Datenbank,4)=2015

subfield(Datum_Datenbank,'-',1)=2015

Regards

Re: problems with query for datefield = 2015

Fri, 22 May 2015 10:32:32 GMT

the first and the second idea doesn't work and the third is still running...

Re: problems with query for datefield = 2015

buzzy996 — Fri, 22 May 2015 10:37:06 GMT

are u tried tis way?

Year(Date(Datum_Datenbank,'YYYY-MM-DD'))=2015

with out #.

Re: problems with query for datefield = 2015

Fri, 22 May 2015 10:39:43 GMT

Yes, did it! But nothing works...

Re: problems with query for datefield = 2015

buzzy996 — Fri, 22 May 2015 10:42:31 GMT

can u create a text object on ur sheet with tis expression and let me know what is the output ur getting?

Re: problems with query for datefield = 2015

anbu1984 — Fri, 22 May 2015 10:43:42 GMT

What do you mean by doesn't work? Is it fetching all data or nothing?

Can you try below expr in Load statement and check the result instead of mentioning it in where clause?

Year(Date#(Datum_Datenbank,'YYYY-MM-DD'))

Re: problems with query for datefield = 2015

anbu1984 — Fri, 22 May 2015 10:47:44 GMT

If nothing works, can you post QVW with sample data using below statement?

Load * From Qvd.qvd(qvd) Where RecNo() < 10;

Re: problems with query for datefield = 2015

Fri, 22 May 2015 10:54:34 GMT

try this one:

Year(Date#(Datum_Datenbank,'YYYY-MM-DD'))=Year(Date#(Today(),'YYYY-MM-DD'))

sorry takeout the hash

Year(Date(Datum_Datenbank,'YYYY-MM-DD'))=Year(Date(Today(),'YYYY-MM-DD'))

and also the below seems to work for me too.

Date(Datum_Datenbank,'YYYY'))=Date(Today(),'YYYY'))

Re: problems with query for datefield = 2015

Fri, 22 May 2015 11:06:32 GMT

subfield(Datum_Datenbank,'-',1)=2015

is the solution!

Thank you so much!!!!!

Re: problems with query for datefield = 2015

Fri, 22 May 2015 11:07:07 GMT

subfield(Datum_Datenbank,'-',1)=2015

is the solution!

Thank you so much!!!!!