topic Re: Converting SQL code to Qliksense scripts in Featured Members

Converting SQL code to Qliksense scripts

amruthakhd — Tue, 29 Aug 2023 16:37:50 GMT

Where can I learn about Qlik sense scripting?

Re: Converting SQL code to Qliksense scripts

zhaofeng — Wed, 30 Aug 2023 02:47:57 GMT

You can try this

T1: Load MANumber, PFINumber, HUBAsOfDate, date(EntryDateTime) as date, SubProductCode as [Product], NoteRate*100 as [Rate], 0 as [PriceDC], sum(DeliveryAmount/1000000) as [DC], 0 as [Funded] from [lib:\\yourpath\\DC.qvd](qvd) where ( SubProductCode = 'FX15' or SubProductCode = 'FX30' or SubProductCode = 'FX20' or SubProductCode = 'FX30HB' or SubProductCode = 'GL30' or SubProductCode = 'GL15' ) and (MONTH(DC.EntryDateTime) = MONTH(@PreviousBusinessDate) AND YEAR(DC.EntryDateTime) = YEAR(@PreviousBusinessDate) ) group by MANumber, PFINumber, HUBAsOfDate date(EntryDateTime), SubProductCode ; inner join(T1) Load distinct MANumber, PFINumber, HUBAsOfDate from [lib:\\yourpath\\MC.qvd](qvd) where ProgramCode<>'STND' ; T2: Load date, [Product], [Rate], sum(0) as [PriceDC], sum(DC) as [DC], sum(0) as [Funded] sum(0) as [UnadjCOAS], sum(0) as [TargetSpread], sum(0) as [ActualSpreadReq], sum(0) as [UnadjLOAS], sum(0) as [COAS], sum(0) as [Yield], sum(0) as [PriceAdj], sum(0) as [CAPriceAdj], sum(0) as [NIS], sum(0) as [MinimumSpread] Resident T1 group by date, [Product], [Rate] ; Drop Table T1;

Re: Converting SQL code to Qliksense scripts

amruthakhd — Tue, 29 Aug 2023 14:50:08 GMT

Thanks a lot. I do understand the structure better. But there are couple of errors. The whole code is actually very huge this is a snippet. Could you direct me to resources where I can learn to script in Qlik sense.

Re: Converting SQL code to Qliksense scripts

zhaofeng — Wed, 30 Aug 2023 02:45:35 GMT

Qlik Sense Script is based on SQL Server.The grammar is generally same but a little different in function.

I think, you can make every subquery as a temp table to transform your code.In my code,I make MC and DC as two different tables then do inner join.That's it.

Learning Qlik Sense Script is a long trip but take it easy.

Re: Converting SQL code to Qliksense scripts

zhaofeng — Wed, 30 Aug 2023 02:49:17 GMT

By the way,I missed some character in my code and I've made some update,please check it.