Change sql code to qlik script

[sevvalk]

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)

[Adam_Romanowski]

It seems you have forgot to paste the code! 😉

[sevvalk]

I am so so sorry 😁. I added.

[MatheusC]

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.ht...

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

[Bjorn_Wedbratt]

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 

[MatheusC]

@sevvalk ,

Did you get the solution?

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

Thank you!

[Or]

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