topic Re: Group between dates from Resident Table in QlikView

Group between dates from Resident Table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone, hope someone can help me. I have a table with four fields, CodArt, FromDate, ToDate and Price. As you can see in the following picture I have several records of the same CodArt and same Price, but with different FromDate values. What I'm trying to achieve is reduce the first table to the second one.

Thank you in advance

Mensaje editado por: Pedro Burgo

Re: Group between dates from Resident Table

vinieme12 — Thu, 30 Mar 2017 08:37:30 GMT

can you post sample excel?

Re: Group between dates from Resident Table

Anonymous — Thu, 30 Mar 2017 09:11:52 GMT

There it is. Thanks for your time

Re: Group between dates from Resident Table

Anonymous — Thu, 30 Mar 2017 09:26:20 GMT

Hi ,

May be this will help you.

Add dimension CodArt and price

Exp-From date

aggr(min(FromDate),Price,CodArt)

To date

aggr(max(ToDate),Price,CodArt)

Thanks

Paridhi

Re: Group between dates from Resident Table

Anonymous — Thu, 30 Mar 2017 09:39:37 GMT

Hi Paridhi, I need to get the second table while loading the data in the script in order to use it later. I'm not trying to get a graphic table.

Sorry if I didn´t make myself clear enough.

Thanks

Re: Group between dates from Resident Table

varshavig12 — Thu, 30 Mar 2017 10:21:04 GMT

load date(min(FromDate)) as FromDate, date(max(ToDate)) as ToDate,CodArt,Price

group by CodArt,Price;

LOAD * INLINE [

CodArt, FromDate, ToDate, Price

E448, 02-01-16, 03-01-16, 1.82

E448, 04-01-16, 04-01-16, 1.82

E448, 05-01-16, 06-01-16, 1.82

E448, 07-01-16, 10-01-16, 1.82

E448, 11-01-16, 11-01-16, 1.45

E448, 12-01-16, 14-01-16, 1.45

E448, 15-01-16, 17-01-16, 1.45

E448, 18-01-16, 18-01-16, 1.75

A150, 02-01-16, 04-01-16, 2.00

A150, 05-01-16, 05-01-16, 2.00

A150, 06-01-16, 07-01-16, 2.00

A150, 08-01-16, 10-01-16, 3.00

];

Re: Group between dates from Resident Table

varshavig12 — Thu, 30 Mar 2017 10:22:18 GMT

Re: Group between dates from Resident Table

Anonymous — Thu, 30 Mar 2017 10:32:43 GMT

Yeah It works, Thank you Varsha

Re: Group between dates from Resident Table

Anonymous — Thu, 30 Mar 2017 11:34:22 GMT

Hi,

PFB

LOAD CodArt,

max(Date(FromDate)) as FromDate,

min(Date(ToDate) ) as ToDate,

Price

FROM

(ooxml, embedded labels, table is Hoja1)

Group By CodArt , Price;