How to get the ID

[#1]

Hi All,

I have one qvd which stores the following data:

Id,Date

1,2013-1-1

2,2013-1-2

3,2013-1-3

4,2014-3-1

5,2014-3-1

What I want is: if the dates are within the same month and the same year, I want to apply the ID from the first day of the month to other date:

The result should be

Id,Date

1,2013-1-1

1,2013-1-2

1,2013-1-3

4,2014-3-1

4,2014-3-3

How can I achieve it?  Thanks.

[rubenmarin]

Hi Isaac, for getting the id from the first date of each Month and Year loaded you can use:

Dates_tmp:

LOAD If(Peek(Month)=Month and Peek(Year)=Year, Peek(Id), Id) as Id,

    Date,

    Month,

    Year;

LOAD Id, Date(Date) as Date, Month(Date) as Month, Year(Date) as Year;

LOAD * Inline [

Id,Date

1,2013-1-1

2,2013-1-2

3,2013-1-3

4,2014-3-1

5,2014-3-2

];

Dates:

NoConcatenate LOAD Id, Date Resident Dates_tmp;

DROP Table Dates_tmp;

[Anonymous]

this should work:

data:
load * inline [
Id,Date
1,2013-1-1
2,2013-1-2
3,2013-1-3
4,2014-3-1
5,2014-3-1
];

Result:
LOAD
Id,
Date,
if(month(Date)=month(Previous(Date)), peek('ID'),Id) as ID
Resident data
ORDER BY Date;