topic Re: Problems With Store Statment in QlikView

Problems With Store Statment

Wed, 25 Nov 2020 16:16:04 GMT

I have an error that I cannot resolve. Below is the outlined error along with my SQL qvd load script. Any help narrowing down how to resolve this error is greatly appreciated.

Error:

Table not found STORE WorkGap into [..\QVD\01_Extract\WorkGap.qvd](qvd)

QVD Script:

WorkGap:
SQL
IF OBJECT_ID('tempdb..#transfers') IS NOT NULL
drop table #transfers
select tx.BrAcct
,tx.BrNbr
,tx.OldBrAcct
,case when LEN(tx.OldBrAcct) = 7 then LEFT(tx.OldBrAcct,1)
when LEN(tx.OldBrAcct) = 8 then LEFT(tx.OldBrAcct,2)
when LEN(tx.OldBrAcct) = 9 then LEFT(tx.OldBrAcct,3)
end as OldBrNbr
,tx.ConversionDt
,DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, tx.ConversionDt) + 1, 0)) as ConversionDtEOM

into #transfers

FROM Reports.dbo.Transfers tx with (nolock)

where tx.OldBrAcct is not null
and tx.BrNbr = 22
and tx.ConversionDt between DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and GETDATE()
IF OBJECT_ID('tempdb..#EOMDelq') IS NOT NULL
Drop Table #EOMDelq
Select
LA.BrAcct
,LA.BrNbr
,CASE WHEN LA.Delq = 0 THEN 'PaidAhead' WHEN LA.Delq IN (1,2) THEN 'CurrentMonth' WHEN LA.Delq = 3 THEN 'P30' WHEN LA.Delq = 6 THEN '30Day' WHEN LA.Delq = 9 THEN '60Day'
WHEN LA.Delq = 12 THEN '90Day' WHEN LA.Delq = 15 THEN '120Day' WHEN LA.Delq = 18 THEN '150Day' WHEN LA.Delq = 21 THEN '180Day'
ELSE 'PaidAhead' END as Delq
Into #EOMDelq
From Reports.dbo.LoanArchive LA with(nolock)
WHERE LA.MonthEnd = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)
IF OBJECT_ID('tempdb..#CollectionComments') IS NOT NULL
Drop Table #CollectionComments
Select
MAX(convert(date,CC.ActionDt,101)) as ActionDate
,CC.BrAcct
Into #CollectionComments
From Reports.dbo.Collections CC    with(nolock)
Where CC.ActionCd IN ('NP','PP','F10')
Group By
CC.BrAcct

Select convert(date,GETDATE(),101) as 'AsOfDate'
,LN.BrAcct
,LD.Balance
,BW.LastName
,BW.FirstName
,BW.MiddleName
,convert(date,LD.DtLstPd,101) as DtLstPd
,convert(date,LD.NxtDueDt,101) as NxtDueDt
,CASE WHEN LN.CollateralCd = 221 THEN 'Secured -- Auto'
WHEN LN.CollateralCd = 235 THEN 'Unsecured'
WHEN LN.CollateralCd = 240 THEN 'Secured -- Personal Property'
WHEN LN.CollateralCd in(260,262) THEN 'Secured -- Boat Motorcycle or RV'
ELSE 'Other'
END AS SecurityType
,CASE WHEN ED.BrNbr = 22 THEN 'Y' ELSE 'N' END AS 'PriorEOMBr22?'
,ED.Delq as PriorEOMDelq
,CASE WHEN LD.Delq = 0 THEN 'CurrentMonth' WHEN LD.Delq IN (1,2) THEN 'P30' WHEN LD.Delq = 3 THEN '30Day' WHEN LD.Delq = 6 THEN '60Day' WHEN LD.Delq = 9 THEN '90Day'
WHEN LD.Delq = 12 THEN '120Day' WHEN LD.Delq = 15 THEN '150Day' WHEN LD.Delq = 18 THEN '180Day' WHEN LD.Delq = 21 THEN '210Day'
ELSE 'PaidAhead' END as Delq
,CASE WHEN LD.LegalStat > 0 and LD.JDDate IS NULL THEN 1 ELSE NULL END as LglFlag
,CASE WHEN LD.JDDate IS NOT NULL and LD.LegalStat = 0 THEN 1 ELSE NULL END as JDFlag
,CASE WHEN LD.LegalStat > 0 and LD.JDDate IS NOT NULL THEN 1 ELSE NULL END as LglJDFlag
,CASE WHEN BK.BrAcct IS NOT NULL THEN 1 Else Null END as BKFlag
,CASE WHEN BK.BrAcct IS NOT NULL OR LD.JDDate IS NOT NULL OR AC.BrAcct IS NOT NULL THEN 'Non-Workable' Else 'Workable' END AS 'AccountStatus'
,CC.ActionDate as 'LstAction(PP,NP,F10)'
,convert(Date,FW.QADLCM,101) as DtLstWorked
,convert(date,GETDATE(),101) as Today
,DATEDIFF(dd,FW.QADLCM,GETDATE()) as 'Days'
,CASE WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 8 THEN '1'
WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 15 THEN '2'
WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 22 THEN '3'
WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 29 THEN '4'
ELSE '5' END AS DaysBucket
,MAX(CONVERT(DATE,FW.FollowupDate,101)) as FollowupDt
,FW.CollDescr + FW.CollDescr_1 + FW.CollDescr_2 + FW.CollDescr_3 as CollectionDescrip
FROM Reports.dbo.Loans LN with(nolock)
LEFT OUTER JOIN Reports.dbo.[Type] TP with (nolock)
on LN.LoanTyp = TP.[Type]
AND LN.Class = TP.Class
LEFT OUTER JOIN Reports.dbo.LoanDaily LD with(nolock)
on LN.BrAcct = LD.BrAcct
LEFT OUTER JOIN Reports.dbo.Bankrupt BK with(nolock)
on BK.BrAcct = LN.BrAcct
and BK.DtFiled is not null
and BK.DtFiled > '01/01/1900'
LEFT OUTER JOIN #CollectionComments CC with(nolock)
on CC.BrAcct = LN.BrAcct
LEFT OUTER JOIN Reports.dbo.Followups FW with(nolock)
on FW.BrAcct = LN.BrAcct
LEFT OUTER JOIN Reports.dbo.LoansBorrower      LB with (nolock)
on LN.BrAcct = LB.BrAcct
and LB.IRSOwner = 'y'
LEFT OUTER JOIN Reports.dbo.Borrower       BW with (nolock)
on LB.HHNbr = BW.HHNbr
and LB.NamSeq = BW.NamSeq
LEFT OUTER JOIN #transfers tx with (nolock)
on LN.BrAcct = tx.BrAcct
LEFT OUTER JOIN #EOMDelq ED with (nolock)
on isnull(tx.OldBrAcct, ld.BrAcct) = ED.BrAcct
LEFT OUTER JOIN Reports.dbo.ActionCodes      AC with (nolock)
on AC.BrAcct = LN.BrAcct
and AC.Code = 250

WHERE LD.Released = 0
AND LD.Retracted = 0
AND LD.Balance > 0
AND LD.Category < 80
AND LD.BrNbr = 22

Group By
LN.BrAcct
,LD.Balance
,BW.LastName
,BW.FirstName
,BW.MiddleName
,convert(date,LD.DtLstPd,101)
,convert(date,LD.NxtDueDt,101)
,CASE WHEN LN.CollateralCd = 221 THEN 'Secured -- Auto'
WHEN LN.CollateralCd = 235 THEN 'Unsecured'
WHEN LN.CollateralCd = 240 THEN 'Secured -- Personal Property'
WHEN LN.CollateralCd in(260,262) THEN 'Secured -- Boat Motorcycle or RV'
ELSE 'Other'
END
,CASE WHEN ED.BrNbr = 22 THEN 'Y' ELSE 'N' END
,ED.Delq
,CASE WHEN LD.Delq = 0 THEN 'CurrentMonth' WHEN LD.Delq IN (1,2) THEN 'P30' WHEN LD.Delq = 3 THEN '30Day' WHEN LD.Delq = 6 THEN '60Day' WHEN LD.Delq = 9 THEN '90Day'
WHEN LD.Delq = 12 THEN '120Day' WHEN LD.Delq = 15 THEN '150Day' WHEN LD.Delq = 18 THEN '180Day' WHEN LD.Delq = 21 THEN '210Day'
ELSE 'PaidAhead' END
,CASE WHEN LD.LegalStat > 0 and LD.JDDate IS NULL THEN 1 ELSE NULL END
,CASE WHEN LD.JDDate IS NOT NULL and LD.LegalStat = 0 THEN 1 ELSE NULL END
,CASE WHEN LD.LegalStat > 0 and LD.JDDate IS NOT NULL THEN 1 ELSE NULL END
,CASE WHEN BK.BrAcct IS NOT NULL THEN 1 Else Null END
,CASE WHEN BK.BrAcct IS NOT NULL OR LD.JDDate IS NOT NULL OR AC.BrAcct IS NOT NULL THEN 'Non-Workable' Else 'Workable' END
,CC.ActionDate
,convert(Date,FW.QADLCM,101)
,DATEDIFF(dd,FW.QADLCM,GETDATE())
,CASE WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 8 THEN '1'
WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 15 THEN '2'
WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 22 THEN '3'
WHEN DATEDIFF(dd,FW.QADLCM,GETDATE()) < 29 THEN '4'
ELSE '5' END
,FW.CollDescr + FW.CollDescr_1 + FW.CollDescr_2 + FW.CollDescr_3;

STORE WorkGap into [..\QVD\01_Extract\WorkGap.qvd](qvd);
DROP TABLE WorkGap;

Re: Problems With Store Statment

swuehl — Tue, 29 Mar 2016 19:53:34 GMT

That's a quite lengthy SQL statement.

First step, add an

Exit script;

statement in the line above the STORE statement.

Run your script. Check the table view using CTRL-T if there is a table created called WorkGap. How many records has this table?

Re: Problems With Store Statment

Tue, 29 Mar 2016 20:10:12 GMT

Thanks for the help the exit statement worked!

Re: Problems With Store Statment

swuehl — Tue, 29 Mar 2016 20:51:25 GMT

Yes, but have you also resolved your issue?

Re: Problems With Store Statment

Tue, 29 Mar 2016 21:10:27 GMT

Yea, you're right, I probably jumped the gun here.

I'm still having problems getting the data to load.

I solved the problem of getting the .qvd to run, when I added the exit script. But when I put my load statment into my .qvw and ran the script I keep getting "Field not Found" error. But I have lined the naming conventions up and they all match the .qvd naming conventions, so I dont know what the problem is now. Any help?

Re: Problems With Store Statment

swuehl — Wed, 30 Mar 2016 09:48:53 GMT

After inserting the exit script statement, have you checked your data model?

Have you seen a table named WorkGap with records?

Or could you post your document log?

Re: Problems With Store Statment

Wed, 30 Mar 2016 13:02:43 GMT

I can't get the script to model anything in the .qvw, because I keep getting the "Field Not Found" error in the .qvw. I have posted the document log from the .qvd showing that it is running, but I still can't get it to pull over in the load script. I have attached the load script as well.

//WorkGap:

//Load AsOfDate

//,BrAcct

//,Balance

//,LastName

//,FirstName

//,MiddleName

//,DtLstPd

//,NxtDueDt

//,SecurityType

//,PriorEOMBr22

//,PriorEOMDelq

//,Delq

//,LglFlag

//,JDFlag

//,LglJDFlag

//,BKFlag

//,AccountStatus

//,LstAction

//,DtLstWorked

//,Today

//,Days

//,DaysBucket

//,FollowupDt

//,CollectionDescrip

//FROM [..\QVD\01_Extract\WorkGap.qvd] (qvd);

Re: Problems With Store Statment

swuehl — Wed, 30 Mar 2016 15:02:04 GMT

Mitchell,

I can't see your attachments, could you check?

Re: Problems With Store Statment

Wed, 30 Mar 2016 18:09:06 GMT

Sorry about that posted is the log from my reload this morning. Thanks again.