topic Re: How to get the ID in QlikView

How to get the ID

Mon, 02 Feb 2015 15:20:33 GMT

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 — Mon, 02 Feb 2015 15:40:54 GMT

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 — Mon, 02 Feb 2015 15:50:38 GMT

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;