Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problems With Store Statment

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;

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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?

View solution in original post

8 Replies
swuehl
MVP
MVP

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?

Not applicable
Author

Thanks for the help the exit statement worked!

swuehl
MVP
MVP

Yes, but have you also resolved your issue?

Not applicable
Author

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?

swuehl
MVP
MVP

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?

Not applicable
Author

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);

swuehl
MVP
MVP

Mitchell,

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

Not applicable
Author

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