Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get the ID

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.

2 Replies
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;

mov
Champion III
Champion III

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;