topic Re: Change sql code to qlik script in App Development

Change sql code to qlik script

sevvalk — Fri, 15 Nov 2024 21:05:51 GMT

How can i change this sql code to qlik script?

Giriş miktarı hesaplama

(SELECT Sum(Nvl(yedmik,0) + Nvl(yedbmk,0) - Nvl(yedimk,0) - Nvl(yedibm,0)) FROM satyed,satsvd WHERE yedkun = svdunq AND svdkun = spdunq)

Çıkış miktarı hesaplama

(SELECT Sum(Nvl(cldmik,0) + Nvl(cldbmk,0)) cldmik FROM satcld,satsvd WHERE cldkun = svdunq AND svdkun = spdunq)

Re: Change sql code to qlik script

Adam_Romanowski — Fri, 26 Jan 2024 14:05:04 GMT

It seems you have forgot to paste the code! 😉

Re: Change sql code to qlik script

sevvalk — Fri, 26 Jan 2024 14:07:14 GMT

I am so so sorry 😁. I added.

Re: Change sql code to qlik script

MatheusC — Fri, 26 Jan 2024 19:32:06 GMT

Hi, @sevvalk

Basically it will be very similar both Where | and are already used within the Load.

Example where | and, documentation below
https://help.qlik.com/pt-BR/cloud-services/Subsystems/Hub/Content/Sense_Hub/Tutorials/reduce-data.htm

I believe that you will only have to change the question of the Nvl that we could be using the Isnull, as an example below:

if(isnull(yedmik),0,yedmik)

Regarts,
Matheus

Re: Change sql code to qlik script

Bjorn_Wedbratt — Sat, 27 Jan 2024 19:17:30 GMT

Hi @sevvalk ,

You could try something along the following:

// Load the data from the tables satyed and satsvd
satyed:
LOAD
yedkun,
yedmik,
yedbmk,
yedimk,
yedibm
FROM
[lib://satyed];

satsvd:
LOAD
svdunq,
svdkun
FROM
[lib://satsvd];

// Join the tables on the condition yedkun = svdunq
JOIN (satyed)
LOAD
svdunq as yedkun,
svdkun
RESIDENT satsvd;

// Drop the table satsvd as it is no longer needed
DROP TABLE satsvd;

// Calculate the sum of the expression using the If and IsNull functions
// The If function returns a value based on a condition
// The IsNull function checks if a value is null or not
LOAD
Sum(If(IsNull(yedmik), 0, yedmik) + If(IsNull(yedbmk), 0, yedbmk) - If(IsNull(yedimk), 0, yedimk) - If(IsNull(yedibm), 0, yedibm)) as Sum
RESIDENT satyed
WHERE svdkun = spdunq;

Although I would recommend you keep the SQL statement in your Qlik script with a proceeding Load statement:

Load *;

SQL SELECT Sum(Nvl(yedmik,0) + Nvl(yedbmk,0) - Nvl(yedimk,0) - Nvl(yedibm,0)) FROM satyed,satsvd WHERE yedkun = svdunq AND svdkun = spdunq;

Letting the source database take care of joining and filtering values will improve performance both with regards to memory consumption as well as bandwidth usage.

Hope this helps.

Best Björn

Re: Change sql code to qlik script

MatheusC — Tue, 30 Jan 2024 12:44:07 GMT

@sevvalk ,

Did you get the solution?

Close the topic with the solution met and leave a like if you found it useful.

Thank you!

Re: Change sql code to qlik script

Or — Tue, 30 Jan 2024 12:50:58 GMT

Or, instead of writing a bunch of if() statements, you could just use RangeSum(Field1,Field2,Field3)...