Group between dates from Resident Table

[Anonymous]

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

[vinieme12]

can you post sample excel?

[Anonymous]

There it is. Thanks for your time

[Anonymous]

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

[Anonymous]

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

[varshavig12 · Accepted solution]

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

];

[varshavig12]

[Anonymous]

Yeah It works, Thank you Varsha

[Anonymous]

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;