Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

mismatch of date formats with SQL date() function and qlikview variable

Hi everybody,

I am trying to use the where clause in SQL to select data from ODBC. The a.sh_sf_dt field is a timestamp the expression date(a.sh_sf_dt) gives me the DD.MM.YYYY format. I try running the following code and get the attached error message. Any idea how I can solve this??

My goal is to put this in a loop so that I can get the inventory quantities by item at the end of each date. That's why it's important for me to be able to say $(vSnapShot) + i.

let vSnapShot = Date#('01.01.2012', DD.MM.YYYY);

Load *, brutStok-standartStok as kMetrajStok;

  SQL

  SELECT   a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,

  sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

  sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

  WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

  ELSE 0*a.sh_qty_mt END)as standartStok

        FROM DBA.stkhar AS a

        LEFT JOIN DBA.mam AS b

        ON a.sh_ma_kod = b.ma_kod

    WHERE date(a.sh_sf_dt) <= $(vSnapShot)

        GROUP BY   snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

        order by   snapDate, ma_kod, desen, varyant

  ;

Thanks for helping.

5 Replies
Miguel_Angel_Baeyens
Not applicable

Re: mismatch of date formats with SQL date() function and qlikview variable

Hi,

Try just wraping the variable with single quotes:

WHERE date(a.sh_sf_dt) <= '$(vSnapShot)'

Hope that helps.

Miguel

Not applicable

Re: mismatch of date formats with SQL date() function and qlikview variable

Hi Miguel I've already tried that but I get the following error messge:

It looks like even though I am using the date() function in SQL it still stays as a timestamp.

SQL##f - SqlState: 07006, ErrorCode: 4294967139, ErrorMsg: [Sybase][ODBC Driver][Adaptive Server Anywhere]Cannot convert 01.01.2012 to a timestamp

SQL

 

          SELECT   a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,

                                        sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

                                        sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

                                                             WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

                                                             ELSE 0*a.sh_qty_mt END)as standartStok

                 FROM DBA.stkhar AS a

                 LEFT JOIN DBA.mam AS b

                 ON a.sh_ma_kod = b.ma_kod

                

                       WHERE date(a.sh_sf_dt) <= '01.01.2012'

                 GROUP BY   snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

                 order by   snapDate, ma_kod, desen, varyant

Miguel_Angel_Baeyens
Not applicable

Re: mismatch of date formats with SQL date() function and qlikview variable

Hi,

Is the "a.sh_sf_dt" field a datetime field or a timestamp field? If the former, then make sure that the input format  "MM.DD.YYYY" is the one that field expects (instead of i.e.: "MDY" or "MM-DD-YYYY"). If the latter, timestamp data types have nothing to do with actual date or time fields, so you will not be able to use that field for comparing...

Hope that helps.

Miguel

Not applicable

Re: mismatch of date formats with SQL date() function and qlikview variable

Hi Miguel,

Thanks for the information. I was able to solve the problem by using the following at the end. Strings are easier to manage than dates .

let vSnapShot=Date#('01.01.2012', DD.MM.YYYY);

stok:

                    Load *, brutStok-standartStok as kMetrajStok;

                    SQL

                              SELECT   a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,

                                                            sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

                                                            sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

                                                                                 WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

                                                                                 ELSE 0*a.sh_qty_mt END)as standartStok

                                     FROM DBA.stkhar AS a

                                     LEFT JOIN DBA.mam AS b

                                     ON a.sh_ma_kod = b.ma_kod

                                     WHERE a.sh_yil = 2012 AND substr(a.sh_sf_dt,9,2)||'.'||substr(a.sh_sf_dt,6,2)||'.'||substr(a.sh_sf_dt,1,4) <= '$(vSnapShot)'

                                     GROUP BY   snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

                                     order by   snapDate, ma_kod, desen, varyant

                              ;


Now I am trying to put this in a loop and I use the following but I get  an error message saying:

Script line error:

for i=0

Any ideas for this one??

let vSnapShot=Date#('01.01.2012', DD.MM.YYYY);

for i=0

          do while $(vSnapShot) <= Date#('05.01.2012', DD.MM.YYYY)/*date(Date#(now()))*/

                    let vSnapShot =date(Date#('01.01.2012', DD.MM.YYYY)+$(i));

                    stok:

                    //sh_sf_hk_kod > 30 stok çıkışı; sh_sf_hk_kod < 30 stok girişi

                    Load *, brutStok-standartStok as kMetrajStok;

                    SQL

                              SELECT   a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,

                                                            sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

                                                            sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

                                                                                 WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

                                                                                 ELSE 0*a.sh_qty_mt END)as standartStok

                                     FROM DBA.stkhar AS a

                                     LEFT JOIN DBA.mam AS b

                                     ON a.sh_ma_kod = b.ma_kod

                                     //WHERE a.sh_yil = 2012 AND ma_kod = 'ME1050-01' AND a.sh_sf_ay = 8

                                     WHERE a.sh_yil = 2012 AND substr(a.sh_sf_dt,9,2)||'.'||substr(a.sh_sf_dt,6,2)||'.'||substr(a.sh_sf_dt,1,4) <= '$(vSnapShot)'

                                           //WHERE a.sh_yil = 2012 AND left(a.sh_sf_dt,10) <= '$(vSnapShot)'

                                     GROUP BY   snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

                                     order by   snapDate, ma_kod, desen, varyant

                              ;

                    let i =$(i)+1;

          loop;


Thanks again for all your help.

Miguel_Angel_Baeyens
Not applicable

Re: mismatch of date formats with SQL date() function and qlikview variable

Hi,

Glad to know that that was helpful and now it works.

And yes, the FOR loop in QlikView has a different syntax than in your script. Actually, you are using a DO WHILE loop, so you don't need to use the FOR line. Anyway, should you would, the syntax is

FOR i = 0 TO 10

... // script here

NEXT

You don't have the TO part in the FOR, that's why QlikView is showing the error.

Hope that helps.

Miguel