topic Re: Table links in QlikView

Table links

Wed, 25 Nov 2020 16:16:04 GMT

I have the following in my load

Table1

SQL SELECT

InvoiceNo

CustNo

DueDate

CASE when [DueDate]is NOT NULL then GETDATE() end AS Today,

CASE WHEN DATEDIFF(DAY,DueDate,GETDATE())>183 THEN

DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY,-183,GETDATE())) + 1, 0) - 1

ELSE

DATEADD(MONTH, DATEDIFF(MONTH, 0, DueDate) + 1, 0) - 1

END AS EndOfMonth,

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) - 1 AS EndOfCurrentMonth

Then I have a resident table based on the above:

Status:

LOAD

IF ([EndOfMonth]<[Today],'Overdue',

IF ([EndOfMonth]<=[EndOfCurrentMonth], 'FALLING DUE BY END OF MONTH','Not Yet Due'))AS Status

Resident table1;

How do I join the two to get the status against each line ?

Thanks!

Re: Table links

sunny_talwar — Tue, 19 Apr 2016 10:42:46 GMT

Try Preceding Load

Table1:

LOAD *,

If([EndOfMonth]<[Today],'Overdue',

If([EndOfMonth]<=[EndOfCurrentMonth], 'FALLING DUE BY END OF MONTH','Not Yet Due')) as Status;

SQL SELECT

InvoiceNo

CustNo

DueDate

CASE when [DueDate]is NOT NULL then GETDATE() end as Today,

CASE WHEN DATEDIFF(DAY,DueDate,GETDATE())>183 THEN

DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY,-183,GETDATE())) + 1, 0) - 1

ELSE

DATEADD(MONTH, DATEDIFF(MONTH, 0, DueDate) + 1, 0) - 1

END AS EndOfMonth,

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) - 1 AS EndOfCurrentMonth

UPDATE: Added the * in the preceding load as pointed out by Colin

Re: Table links

Colin-Albert — Tue, 19 Apr 2016 10:55:49 GMT

I think you missed the * from the preceeding load Sunny.

Table1:

LOAD If([EndOfMonth]<[Today],'Overdue',

If([EndOfMonth]<=[EndOfCurrentMonth], 'FALLING DUE BY END OF MONTH','Not Yet Due')) as Status,

SQL SELECT

InvoiceNo

CustNo

DueDate

CASE when [DueDate]is NOT NULL then GETDATE() end as Today,

CASE WHEN DATEDIFF(DAY,DueDate,GETDATE())>183 THEN

DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY,-183,GETDATE())) + 1, 0) - 1

ELSE

DATEADD(MONTH, DATEDIFF(MONTH, 0, DueDate) + 1, 0) - 1

END AS EndOfMonth,

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) - 1 AS EndOfCurrentMonth

Re: Table links

sunny_talwar — Tue, 19 Apr 2016 10:59:57 GMT

Yes, I did

Re: Table links

Tue, 19 Apr 2016 11:02:28 GMT

Thank you for your help today.

It has been invaluable.

Re: Table links

sunny_talwar — Tue, 19 Apr 2016 11:03:15 GMT

Not a problem. We are glad that we are able to help you out