topic Two different dates as one in QlikView

Two different dates as one

Mon, 12 Oct 2009 17:33:07 GMT

I have two tables with financial information. One contains information from 01/12-2008 and onwards (LedgerCase) and one contains information from 1978 and onwards (Eco_Comments).

My problems is that for certain transactions I need to take the information from LedgerCase and for others I need to take it from Eco_Comments. The dates are in some cases the same for the transaction in the two tables but in other not and that is what is causing me troubles.

I need to present only one date choice for the user to select, so I have to somehow join the two date into one with conditions.

In the below case I need to take as follows:

- Comment_Date from Eco_Comment for the transaction 07/06-2008 as it is before 01/12-2008

- Reg_Date from LedgerCase where the Amount_Code in Eco_Comments = 1110 and Function_Code =BE

- Comment_Date from Eco_Comment where the Amount_Code in Eco_Comments =1110 and the Function_Code=DB as the DB payment is only in Eco_Comment and not in LedgerCase.

Eco_Comments

CASE_ID	COMMENT_DATE	AMOUNT_CODE	FUNCTION_CODE	AMOUNT	Eco_AmountID
2196902	07-06-2008	1110	BE	-599,00
2196902	07-09-2009	1110	BE	-371,90	3910743
2196902	07-09-2009	1110	BE	-652,00	3910744
2196902	07-09-2009	1110	DB	-500,00	0

LedgerCase

CA_CASE_ID	AMOUNT	DEBIT_ACCT	CREDIT_ACCT	REG_DATE	GIRO_DATE	AMOUNT_ID
2196902	371,90	1170	2103	10-09-2009	06-09-2009	3910743
2196902	652,00	1170	2103	10-09-2009	07-09-2009	3910744

How can I present it to the user so they only have to select one date and not having to think about "If X then I should select on Eco_Comments date, but if Y then I should to it from LedgerCase date"

Any help would be greatly appreciated.

Two different dates as one

Mon, 12 Oct 2009 18:07:39 GMT

Someting like this?

Select
.......,
if(year(COMMENT_DATE)=2008,COMMENT_DATE,if(Eco_Comments = 1110 and Function_Code ='BE',REG_DATE,COMMENT_DATE)) as DATE_COMBO
from Eco_Comments a
left outer join LedgerCase b
on a.CASE_ID=b.CA_CASE_ID;

--Arun

Two different dates as one

Tue, 13 Oct 2009 17:47:44 GMT

Unfortunately my ODBC driver doesn't support select statements. Any other ideas ?

Two different dates as one

Tue, 13 Oct 2009 18:01:42 GMT

Concatinate the two tables as the have the same data but for different periods.

QlikView Reference Manual Page 289

Two different dates as one

Tue, 13 Oct 2009 19:40:17 GMT

Sorry no - that dosen't do the trick, as both tables has dates but under certain circumstances I need the date from Ledger_Case and under other circumstances I need it from Eco_Comments.

I enclose a small qvw to show the result after concatenat. I still have dates 10/09-2009, 07/09-2009, 07/06-2008. I want to end up with only dates 10/09-2009 and 07/06-2008.

Two different dates as one

Tue, 13 Oct 2009 20:01:08 GMT

I don't have Qlikview on the system i am workin on...COuld you explain to me why u don't want 07/09-2009 in ur report

Two different dates as one

Tue, 13 Oct 2009 20:26:20 GMT

Afteryou create the concatination table do the max of the datefield and group it by transaction id or whatever is relavent. And finaly drop the previously concatinate tables.

Something like this......

Concattable:
load datefield a, b, c from table1.csv;
concatenate load datefield, a, c from table2,csv;

LatestConcat:
Load a,, max(Sales) as Latestdate from Concattable group by datefield;

Drop the Concattable;