topic Re: WHERE in a replace load Statement - Help Needed in QlikView

WHERE in a replace load Statement - Help Needed

Anonymous — Tue, 07 Aug 2012 12:40:54 GMT

Hi Community,

I hope you can help me.

I want to load date from an already existing qlikview table using load... resident... where

The load resident clause is working without the "where" thus I figure my error has to be in the where part:

The resident Table has the field [ERG-Datum-Zeit] which is a timestamp. In the new table I only want to load the rows which are within an intervall specified by two variable (schmat_datum_von, schmat_datum_bis) those are timestamps as well.

Here are the where clauses I tried without success:

WHERE "ERG-DatumZeit">=$(schmat_datum_von) and "ERG-DatumZeit"<=$(schmat_datum_bis);

where timestamp#([ERG-DatumZeit],'DD.MM.YYYY hh:mm')>=timestamp#($(schmat_datum_von),'DD.MM.YYYY hh:mm') and timestamp#([ERG-DatumZeitBis],'DD.MM.YYYY hh:mm')<=timestamp#($(schmat_datum_bis),'DD.MM.YYYY hh:mm');

In the Errormessage it complains about an Syntax Error (Fehlerhafte Syntax).

Any Ideas what might be wrong?

Regards,

Matthias

Re: WHERE in a replace load Statement - Help Needed

giakoum — Tue, 07 Aug 2012 12:45:10 GMT

Are the variables there before the where statement? And what if you replace " with [ for the field names?

Re: WHERE in a replace load Statement - Help Needed

Anonymous — Tue, 07 Aug 2012 12:54:43 GMT

The variable are currently coming from an input box.

The error message gives the values of the variables thus I think that the variable values are all right. I guess the problem is related to the field. (This time I included the fieldnames in brackets)

Regards,

Matthias

Re: WHERE in a replace load Statement - Help Needed

giakoum — Tue, 07 Aug 2012 12:59:14 GMT

try this :

WHERE [ERG-DatumZeit]>='$(schmat_datum_von)' and [ERG-DatumZeit]<='$(schmat_datum_bis)'

Re: WHERE in a replace load Statement - Help Needed

Anonymous — Tue, 07 Aug 2012 13:22:07 GMT

Thanks the ' ' did the trick. Though I dont know yet what they do... they work.

I have two more (minor) questions though:

1) Despite the fact, that I am using replace load - if I am running the script partially instead of replacing the table the new data is put into a new table with an interation. Why is that?

2) How can I prevent to get records with null values in my new table? I tried ERG-DatumZeit not null or <>null but those caused errors as well.

- Matthias

Re: WHERE in a replace load Statement - Help Needed

giakoum — Tue, 07 Aug 2012 13:27:08 GMT

2) not isnull(ERG-DatumZeit)

1) not sure, need to have a sample data to check