Implement SQL Query to Load Editor QlikSense for Using While Loop and Temporary Table

[Zaidan_as]

Hello QlikSense Community,

As I stated in the Subject, I want to code this SQL Query to Load Editor in QlikSense.
The problem is, I cannot find the similar while loop code and use the temporary table in load editor QlikSense.

So for the background, I have CSV.file (the name is Report Tickets.csv) that will be imported to QlikSense. The CSV.file illustration is like this.

(sorry I cannot show all the columns)

Then from this CSV.file, I want to make this table

Where "Month" column is from concatenate the Year and Month on CreatedDate field, then "In" column is from count the ID field by CreatedDate field, "Out" is from count the ID field by ClosedDate field, "OUTSTNDG" is from calculate using this formula = "O/B"+"In"-"Closed".
Then for the "O/B", the first data will contain 0 value, and the next one is the "OUTSTNDG" value from month before. the illustration is like this.

So I can get this result using SQL Query like this :

declare
@FilePath Varchar(max)='\\172.29.228.121\Appl\report_tickets.xlsx',
@ImportBy varchar(200)='test'
;

declare
@sql nvarchar(max)

create table #DataRaw
(
ID varchar(max),
[Priority] varchar(max),
[Last Updated Date] datetime,
[Agent Name] varchar(max),
Urgency varchar(max),
[Status] varchar(max),
FirstResponseDate datetime,
Category varchar(max),
[Item Category] varchar(max),
AgentGroupName varchar(max),
ChildTicketsCount varchar(max),
AssignedDate datetime,
Plant varchar(max),
[Subject] varchar(max),
CaseCategory varchar(max),
CreatedDate datetime,
ClosedDate datetime,
SubCategory varchar(max),
DepartmentName varchar(max),
RequesterName varchar(max),
SPLinHouseApps varchar(max),
SSIPApps varchar(max),
SCCInHouseApps varchar(max),
SCCPIC varchar(max),
SBMPIC varchar(max),
SSIPAppsPIC varchar(max),
SBMInHouseApps varchar(max),
SPLPIC varchar(max),
PendingRemark varchar(max)
)

set @sql = 'insert into #DataRaw
select * from OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database=' + @FilePath + ''',[Sheet1$])'
EXECUTE sp_executesql @sql
--exec(@sql) AS Login = 'sa'

select id AS IDa, convert(varchar,left( convert(date, CreatedDate),4))+'-'+substring(convert(varchar,convert(date, CreatedDate)),6,2) as MonthSet,
convert(varchar,left( convert(date, CreatedDate),4))+'-'+substring(convert(varchar,convert(date, CreatedDate)),6,2) as [IN],
convert(varchar,left( convert(date, ClosedDate),4))+'-'+substring(convert(varchar,convert(date, ClosedDate)),6,2) as CLOSED
into #data1
from #DataRaw
where
plant like '%SPL%' and (AgentGroupName not in ('SPL Apps Team', 'SBM Apps Team') or AgentGroupName is null)
order by convert(date, CreatedDate)

create table #dataF
(
ID int identity(1,1),
MonthSet varchar(100),
[O/B] varchar(100),
[IN] int,
Closed int,
Outstanding int
)

insert into #dataF
select distinct monthset, 0,null,null,null from #data1 order by monthset

select
CLOSED as MonthSet,count(CLOSED) as [countClosed]
into #dataclosed
from #data1
group by CLOSED

select MonthSet,count(MonthSet) as [countIN]
into #datain
from #data1
group by MonthSet
order by MonthSet

update a set a.[IN]=b.countIN
from #dataF a
left outer join #datain b on a.MonthSet=b.MonthSet

update a set a.Closed=b.countClosed
from #dataF a
left outer join #dataclosed b on a.MonthSet=b.MonthSet

declare
@min int,
@Max int

select @min=min(ID), @Max=max(ID) from #dataF

while @Max>=@min
begin
update #dataF set Outstanding=isnull([O/B],0)+isnull([IN],0)-isnull(Closed,0) where ID=@min
update b set b.[O/B]=isnull(a.Outstanding,0)
from #dataF a left outer join #dataF b on a.ID+1=b.ID
where a.ID=@min

set @min=@min+1
end

select * from #dataF
order by MonthSet

drop table #DataRaw
drop table #data1
drop table #dataclosed,#datain,#dataF

but when I try to code it on the Load Editor QlikSense, I comes up for many problems, like I cannot using Count, Update formula, and also the temporary table.

So what I want to ask is :
1. Is this problem can be solved ?
2. Is there any easier way beside my flow for this problem ?

[Zaidan_as]

Ok, i already found the solution,

First, I create 2 temp table (data1 and data2) with the data2 is the result from join data1.

data1 :
LOAD
'From data1' as SourceData1,
Year(Timestamp(Timestamp#([Created Date],'YYYY-MM-DD hh:mm:ss TT')))&'-'&Month((Timestamp(Timestamp#([Created Date],'YYYY-MM-DD hh:mm:ss TT')))) as [tesIn],
Year(Timestamp(Timestamp#([Created Date],'YYYY-MM-DD hh:mm:ss TT')))&''&Num(Month((Timestamp(Timestamp#([Created Date],'YYYY-MM-DD hh:mm:ss TT')))), '00') as [tesSort],
Timestamp(Timestamp#([Created Date],'YYYY-MM-DD hh:mm:ss TT')) as [tesCreated],
[Plant] as [tesPlant],
if(len(trim([Agent Name]))=0,'No Agent',[Agent Name]) as [tesName Agent],
UPPER([Agent Group Name]) as [tesAgent Group Name],
if(len(trim([Created Date]))>0,1,0) as [InVal]
FROM [lib://FSDownload (sbm-vmqlik_qsadmin)/report_tickets.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

data2 :
Left Keep(data1)
LOAD
Year(Timestamp(Timestamp#([Closed Date],'YYYY-MM-DD hh:mm:ss TT')))&'-'&Month((Timestamp(Timestamp#([Closed Date],'YYYY-MM-DD hh:mm:ss TT')))) as [tesIn],
Timestamp(Timestamp#([Closed Date],'YYYY-MM-DD hh:mm:ss TT')) as [tesClosed],
[Plant] as [tesPlant],
if(len(trim([Agent Name]))=0,'No Agent',[Agent Name]) as [tesName Agent],
UPPER([Agent Group Name]) as [tesAgent Group Name],
if(len(trim([Closed Date]))>0,1,0) as [OutVal]
FROM [lib://FSDownload (sbm-vmqlik_qsadmin)/report_tickets.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

Then in the sheet, I create  a table, with this parameter.

1. MonthSet = tesIn

2. In = Sum(InVal)

3. Closet = Sum(OutVal)

4. Outstanding = if(len(trim(above(Sum(InVal) - Sum(OutVal))))=0,Sum(InVal) - Sum(OutVal),Sum(InVal) - Sum(OutVal)+RangeSum(above(Sum(InVal)-Sum(OutVal),1,(RowNo()))))

5. O/B = above(if(len(trim(above(Sum(InVal) - Sum(OutVal))))=0,Sum(InVal) - Sum(OutVal),Sum(InVal) - Sum(OutVal)+RangeSum(above(Sum(InVal)-Sum(OutVal),1,(RowNo())))))

And this is the result