Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Table links

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!

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

5 Replies
sunny_talwar

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

Colin-Albert

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

sunny_talwar

Yes, I did

Not applicable
Author

Thank you for your help today.

It has been invaluable.

sunny_talwar

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