Can anyone help me to convert PostgreSQL query into Qliksense scripting

[mahnoor1279]

Below query is for to get the all customers who have some amount on today -1 but on today there balance is 0=zero ,

Attaching data for reference

select ac."CUST_SNO", (sum(Dt."INFLOW") - sum(Dt."OUTFLOW")) "T_BAL",

 (sum(Dt1."INFLOW") - sum(Dt1."OUTFLOW")) "T1_BAL", CURRENT_DATE  as "FLAG"

from amcwh."transaction" Dt , amcwh."transaction" Dt1,

amcwh."CUST_ACCT" ac

,(select distinct cus."CUST_SNO" from amcwh."transaction" r,

amcwh."DIM_CUST_ACCT_D" cus  

where cus."CUST_ACCT_SNO" = r."CUST_ACCT_SNO"

         and  "TRANSACTION_TYPE"='REDEMPTION'

          and TO_CHAR("LEDGER_DATE",'YYYY-MM-DD') = CURRENT_DATE  

         and cus."ACCOUNT_CATEGORY" in ('1','4','5','7')) r

where Dt."CUST_ACCT_SNO" = Dt1."CUST_ACCT_SNO"

and ac."CUST_ACCT_SNO" = Dt."CUST_ACCT_SNO"

and ac."CUST_ACCT_SNO" = Dt1."CUST_ACCT_SNO"  

and TO_CHAR(Dt."LEDGER_DATE",'YYYY-MM-DD')<= CURRENT_DATE  

and TO_CHAR(Dt1."LEDGER_DATE",'YYYY-MM-DD')<= CURRENT_DATE   - 1

and ac."CUST_SNO"  = r."CUST_SNO"

and ac."ACCOUNT_CATEGORY" in ('1','4','5','7')

group by  ac."CUST_SNO"

having (sum(Dt."INFLOW") - sum(Dt."OUTFLOW"))<=1

and (sum(Dt1."INFLOW") - sum(Dt1."OUTFLOW"))>=1

[PrashantSangle]

One of the biggest advantage of qlik is that you can directly use your query in qlik with out conversion.

So in your case, you can directly use it as it is.

Still you want to convert that SQL in code in Qlik script then, I can see in the query developer use 2 table 

transaction & cus.

You can load those 2 table then do join over it & in final table use same where clause what they added in your sql query. 

[mahnoor1279]

I've made this script but it didn't load any records

Data_temp:

LOAD

  CUST_ACCT_SNO,

  LEDGER_DATE,

  SUM(OUTFLOW)-SUM(INFLOW) as BALANCE

FROM [lib://DataExtractor (ahiml-main_qsense)/TRANSACTION.qvd]

(qvd) 

WHERE TRANSACTION_TYPE ='REDEMPTION'

GROUP BY CUST_ACCT_SNO, LEDGER_DATE;

NoConcatenate

Data_temp2:

Load

*

resident Data_temp;

drop table Data_temp;

join

CUST_ACCT:

Load

CUST_ACCT_SNO,

ACCOUNT_CATEGORY

FROM [lib://DataExtractor (ahiml-main_qsense)/cust.qvd]

(qvd) where Match(ACCOUNT_CATEGORY ,1,4,5,7) and  

[STATUS_FLAG] = 'True' and not IsNull(CUST_ACCT_CODE);

NoConcatenate

final:

load *

RESIDENT Data_temp2

WHERE 

  (Date(Floor(LEDGER_DATE)) = Today()-1  AND BALANCE > 1)

   AND (Date(Floor(LEDGER_DATE)) = Today() AND BALANCE < 1)

;

drop table Data_temp2;

[PrashantSangle]

while debugging we have to go with baby steps;

1st load initial table & join table then put exit script & check did you get the data or not

if you are getting data then there must be issue in where clause. Comment that where clause & run it again.

I think there is issue in final where clause

Regards,

Prashant Sangle