Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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

View solution in original post

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