which function to show correct data?

[schultem]

Hi,

I have some troubles making my report. My model is attached. Key is "pers.nr". How can I make a table which shows me the desired result? I tried to make a calculated dimension for column scaled but Qlikview still gives me a row for every single scale. Thanks in advance for your help!

regards,

Mandy

[sunny_talwar]

May be try like this:

Data:

LOAD * Inline [

pesr nr, name

1234, Ed

];

Absence:

LOAD Date(startdate + IterNo() - 1) as AbsenceDays,

  startdate as AbsenceStart,

  enddate as AbsenceEnd,

  [pesr nr]

While startdate + IterNo() - 1 <= enddate; 

LOAD * Inline [

pesr nr, startdate, enddate

1234, 1-1-2016, 2-1-2016

1234, 2-3-2016, 4-3-2016

];

Payment:

LOAD * Inline [

pesr nr, startdate, enddate, scale

1234, 1-12-2016, 31-12-2016, 11

1234, 1-1-2016, 31-1-2016, 11

1234, 1-2-2016, 28-2-2016, 12

1234, 1-3-2016, 31-3-2016, 12

1234, 1-4-2016, 30-4-2016, 12

];

Left Join (Payment)

IntervalMatch(AbsenceDays, [pesr nr])

LOAD startdate,

  enddate,

  [pesr nr]

Resident Payment;