topic Re: Condition Date in QlikView

Condition Date

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

Hey all ,

If I have a table with a daily Date Example:

the table contain ID Balance and Date and the date has form as (01/01/2016)

I need to extract for previous month the Balance as at end of month and for the maximum for current month from the script since the table from database is too large and I want to extract to table with these condition .

How can I write the "Where" Condition ?

Hope you can help

Thanks,

Re: Condition Date

sunny_talwar — Mon, 23 Jan 2017 16:14:47 GMT

Would you be able to share few rows of data and explain what you want to extract from it?

Re: Condition Date

mario-sarkis — Mon, 23 Jan 2017 16:34:26 GMT

Hey Sunny , Thank you for Your Response. unfortunately I can't share my data here but here is an example and hope this will be clear:

let's say each ID has a balance evry day but I need to extract from my database to table that contain the balance only for end of each month and for current month the maximum date (not the whole month) below the table and my output would be red cells hope you can help thank you so much :

ID	Date	Balance
1	01/01/2016	1000
1	12/01/2016	12000
1	26/01/2016	26000
1	27/01/2016	27000
1	28/01/2016	28000
1	29/01/2016	29000
1	30/01/2016	30000
1	31/01/2016	31000
1	01/02/2016	32000
1	16/02/2016	47000
1	17/02/2016	48000
1	18/02/2016	49000
1	19/02/2016	50000
1	20/02/2016	51000
1	21/02/2016	52000
1	22/02/2016	53000
1	23/02/2016	54000
1	24/02/2016	55000
1	25/02/2016	56000
1	26/02/2016	57000
1	27/02/2016	58000
1	28/02/2016	59000
1	29/02/2016	60000
1	01/03/2016	61000
1	02/03/2016	62000
1	03/03/2016	63000
1	04/03/2016	64000
1	05/03/2016	65000
1	06/03/2016	66000
1	07/03/2016	67000
1	08/03/2016	68000
1	09/03/2016	69000
1	10/03/2016	70000
1	11/03/2016	71000
1	12/03/2016	72000
1	13/03/2016	73000
1	14/03/2016	74000
1	15/03/2016	75000
1	16/03/2016	76000

Re: Condition Date

sunny_talwar — Mon, 23 Jan 2017 16:41:28 GMT

May be something like this:

Table:

LOAD ID,

Date,

Date(MonthStart(Date), 'MMM-YYYY') as MonthYear,

Balance

FROM .....;

Right Join (Table)

LOAD ID,

MonthYear,

Max(Date) as Date

Resident Table

Group By ID, MonthYear;

There are multiple other ways to do this... but this should give you a table with only max date for each MonthYear and ID....

Re: Condition Date

mario-sarkis — Mon, 23 Jan 2017 16:49:52 GMT

Ok sunny thanks , let me try is and get bet to you.