topic Re: Microsoft Access Sql transform to qlik sense script in App Development

Microsoft Access Sql transform to qlik sense script

sevvalk — Fri, 18 Oct 2024 06:21:06 GMT

Hi,

This sql written in Microsoft Access. I want to write this sql in qlik sense. The table load method is like this:

fg_salspm:
LOAD spmyil,
spmrfc,
spmref,
spmtrh,
spmony,
spmdrm,
spmckt,
spmchs;
SQL SELECT spmyil,
spmrfc,
spmref,
spmtrh,
spmony,
spmdrm,
spmckt,
spmchs
FROM informix.fg_salspm;

fg_salspd:
LOAD spdyil,
spdrfc,
spdref,
spdmik,
spdbrm,
spdktg,
spdskd,
spdntt,
spditt,
spdkdt;
SQL SELECT spdyil,
spdrfc,
spdref,
spdmik,
spdbrm,
spdktg,
spdskd,
spdntt,
spditt,
spdkdt
FROM informix.fg_salspd;

fg_salflm:
LOAD flmnum,
flmckt,
flmchs,
flmbsl,
flmbts;
SQL SELECT flmnum,
flmckt,
flmchs,
flmbsl,
flmbts
FROM informix.fg_salflm;

-------------- SQL --------------------------

Siparis_S:

SELECT fg_salspm.spmyil, fg_salspm.spmrfc, fg_salspm.spmref, fg_salspm.spmtrh, fg_salspd.spdmik, fg_salspd.spdbrm, fg_salspd.spdktg, fg_salspd.spdskd, fg_salspd.spdntt, fg_salspd.spditt, fg_salspd.spdkdt, fg_salspm.spmckt, fg_salspm.spmchs

FROM fg_salspm LEFT JOIN fg_salspd ON (fg_salspm.spmyil = fg_salspd.spdyil) AND (fg_salspm.spmrfc = fg_salspd.spdrfc) AND (fg_salspm.spmref = fg_salspd.spdref)

WHERE (((fg_salspm.spmtrh)>#1/1/2024#));

FiyatListesi:

SELECT Siparis_S.*, fg_salflm.flmnum, Format([flmbsl],"Short Date") AS İfade1, IIf(IsNull([flmbts]),Date(),Format([flmbts],"Short Date")) AS A

FROM Siparis_S LEFT JOIN fg_salflm ON (Siparis_S.spmckt = fg_salflm.flmckt) AND (Siparis_S.spmchs = fg_salflm.flmchs);

Karsilastirma:

SELECT FiyatListesi_S.*

FROM FiyatListesi_S

WHERE ((([Siparis_S].[spmtrh])>=[İfade1] And ([Siparis_S].[spmtrh])<=[A]));

Can you help me about Access sql transform to qlik sense script?

Re: Microsoft Access Sql transform to qlik sense script

henrikalmen — Fri, 18 Oct 2024 10:31:44 GMT

If you have created a connection (see Connecting to data sources or Connecting to databases) to your database I kind of think those scripts will run in Qlik Sense. Possibly with an error here: WHERE (((fg_salspm.spmtrh)>#1/1/2024#))

What problems do you encounter?

Re: Microsoft Access Sql transform to qlik sense script

sevvalk — Fri, 18 Oct 2024 11:32:26 GMT

The final version of the code is this:

LIB CONNECT TO 'LoginX (Login)';

fg_salspm:
LOAD spmyil,
spmrfc,
spmref,
spmtrh,
spmony,
spmdrm,
spmckt,
spmchs;
SQL SELECT spmyil,
spmrfc,
spmref,
spmtrh,
spmony,
spmdrm,
spmckt,
spmchs
FROM informix.fg_salspm;

fg_salflm:
LOAD flmnum,
flmckt,
flmchs,
flmbsl,
flmbts;
SQL SELECT flmnum,
flmckt,
flmchs,
flmbsl,
flmbts
FROM informix.fg_salflm;

//-------------- SQL --------------------------

Siparis_S:

SELECT
[fg_salspm].[spmyil],
[fg_salspm].[spmrfc],
[fg_salspm].[spmref],
[fg_salspm].[spmtrh],
[fg_salspd].[spdmik],
[fg_salspd].[spdbrm],
[fg_salspd].[spdktg],
[fg_salspd].[spdskd],
[fg_salspd].[spdntt],
[fg_salspd].[spditt],
[fg_salspd].[spdkdt],
[fg_salspm].[spmckt],
[fg_salspm].[spmchs]

FROM
[fg_salspm]
LEFT JOIN
[fg_salspd]
ON
([fg_salspm].[spmyil] = [fg_salspd].[spdyil])
AND ([fg_salspm].[spmrfc] = [fg_salspd].[spdrfc])
AND ([fg_salspm].[spmref] = [fg_salspd].[spdref])

WHERE
[fg_salspm].[spmtrh] > '01.01.2024';

FiyatListesi:
SELECT
[Siparis_S].*,
[fg_salflm].[flmnum],
Format([fg_salflm].[flmbsl], "Short Date") AS [ref_flmbsl],
IIf(IsNull([fg_salflm].[flmbts]), Date(), Format([fg_salflm].[flmbts], "Short Date")) AS [ref_flmbts]

FROM
[Siparis_S]
LEFT JOIN
[fg_salflm]
ON
([Siparis_S].[spmckt] = [fg_salflm].[flmckt])
AND ([Siparis_S].[spmchs] = [fg_salflm].[flmchs]);

Karsilastirma:

SELECT
[FiyatListesi_S].*

FROM
[FiyatListesi_S]

WHERE
([Siparis_S].[spmtrh] >= [ref_flmbsl])
AND ([Siparis_S].[spmtrh] <= [ref_flmbts]);

---------------------------- ERROR -------------------------------

The following error occurred:

Connector reply error: SQL##f - SqlState: 37000, ErrorCode: 4294967095, ErrorMsg: [Informix][Informix ODBC Driver][Informix]A syntax error has occurred.

The error occurred here::

Siparis_S: SELECT [fg_salspm].[spmyil], [fg_salspm].[spmrfc], [fg_salspm].[spmref], [fg_salspm].[spmtrh], [fg_salspd].[spdmik], [fg_salspd].[spdbrm], [fg_salspd].[spdktg], [fg_salspd].[spdskd], [fg_salspd].[spdntt], [fg_salspd].[spditt], [fg_salspd].[spdkdt], [fg_salspm].[spmckt], [fg_salspm].[spmchs] FROM [fg_salspm] LEFT JOIN [fg_salspd] ON ([fg_salspm].[spmyil] = [fg_salspd].[spdyil]) AND ([fg_salspm].[spmrfc] = [fg_salspd].[spdrfc]) AND ([fg_salspm].[spmref] = [fg_salspd].[spdref]) WHERE [fg_salspm].[spmtrh] > '01.01.2024'

Re: Microsoft Access Sql transform to qlik sense script

henrikalmen — Fri, 18 Oct 2024 12:50:45 GMT

The error you are getting is not a problem with the qlik script itself, it's the database that responds with an error. And it seems that the query the databse is trying to run, includes your definition of what the table is supposed to be called when loaded by qlik. Try adding one line like this:

Siparis_S:
LOAD *;
SELECT
[fg_salspm].[spmyil],
...

Re: Microsoft Access Sql transform to qlik sense script

sevvalk — Fri, 18 Oct 2024 13:07:29 GMT

It doesn't work. It given the same error.

Re: Microsoft Access Sql transform to qlik sense script

p_verkooijen — Fri, 18 Oct 2024 15:01:16 GMT

Hi @sevvalk

Does this work? Load * FROM informix.fg_salspm;

Check your FROM in the SQL statements, you are not including informix.

Siparis_S:

FROM
informix.[fg_salspm]
LEFT JOIN [fg_salspd] ON ([fg_salspm].[spmyil] = [fg_salspd].[spdyil])
AND ([fg_salspm].[spmrfc] = [fg_salspd].[spdrfc])
AND ([fg_salspm].[spmref] = [fg_salspd].[spdref])

Re: Microsoft Access Sql transform to qlik sense script

marksouzacosta — Fri, 18 Oct 2024 15:04:41 GMT

Hi @sevvalk,

There are multiple approaches for this problem.

1. You can try to just execute the query as it is, without any modification, and load the results in Qlik. Usually recommend for very complex queries, hard to translate to Qlik Script.

2. You can load all the tables from your source database, store as QVDs and try to reproduce the queries. This is close to your case, you just need to save the QVD files.

Anyway, this is how you can translate your first query:

Siparis_S:
LOAD 
    // Key fields
    spmyil,     
    spmrfc, 
    spmref, 

    // Regular fields
    spmtrh, 
    spmckt, 
    spmchs
RESIDENT 
    fg_salspm 
WHERE 
    spmtrh > MakeDate(2024,1,1)
;

LEFT JOIN (Siparis_S)
fg_salspd:
LOAD
    // Key fields
    spdyil AS spmyil,
    spdrfc AS spmrfc,
    spdref AS spmref,

    // Regular fields
    spdmik, 
    spdbrm, 
    spdktg, 
    spdskd, 
    spdntt, 
    spditt, 
    spdkdt, 
RESIDENT
    fg_salspd
;