Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

pitutiti
New Contributor III

Group between dates from Resident Table

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.

Captura.JPG

Thank you in advance

Mensaje editado por: Pedro Burgo

Tags (2)
1 Solution

Accepted Solutions
varshavig12
Valued Contributor

Re: Group between dates from Resident Table

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

];

View solution in original post

8 Replies
vinieme12
Esteemed Contributor II

Re: Group between dates from Resident Table

can you post sample excel?

pitutiti
New Contributor III

Re: Group between dates from Resident Table

There it is. Thanks for your time

paridhimantri
Contributor

Re: Group between dates from Resident Table

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

pitutiti
New Contributor III

Re: Group between dates from Resident Table

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
Valued Contributor

Re: Group between dates from Resident Table

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

];

View solution in original post

Highlighted
varshavig12
Valued Contributor

Re: Group between dates from Resident Table

pitutiti
New Contributor III

Re: Group between dates from Resident Table

Yeah It works, Thank you Varsha

paridhimantri
Contributor

Re: Group between dates from Resident Table

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;