# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## reduce table rows with date_from and date_to columns

hello,

i have a huge table (3 billions rows) with the cost of product in each store per day.

the cost doesnt change frequently

I want to reduce the number of rows os this table using 2 columns of date interval (date_from and date_to) instead one row per day

original table:

 date store material cost 01/01/2018 a 123 10 02/01/2018 a 123 10 03/01/2018 a 123 100 04/01/2018 a 123 10 05/01/2018 a 123 200 06/01/2018 a 123 200

result table:

 store material cost date_from date_to a 123 10 01/01/2018 02/01/2018 a 123 100 03/01/2018 03/01/2018 a 123 10 04/01/2018 04/01/2018 a 123 200 05/01/2018 06/01/2018

i tried to use group by with min(date) and max(date) but it didnt work (see the case of cost 10 in the example above, I got from 01/01 from 04/01)

Im also trying something with one incremental auxiliar column using previous but without success

 date store material cost aux 01/01/2018 a 123 10 1 02/01/2018 a 123 10 1 03/01/2018 a 123 100 2 04/01/2018 a 123 10 3 05/01/2018 a 123 200 4 06/01/2018 a 123 200 4

does anyone can help me?

Tags (2)
1 Solution

Accepted Solutions
Esteemed Contributor

## Re: reduce table rows with date_from and date_to columns

Try this:

table:

Load date(date#(date,'DD/MM/YYYY')) as date, store, material, cost Inline [

date, store, material, cost

date, store, material, cost

01/01/2018, a, 123, 10

02/01/2018, a, 123, 10

03/01/2018, a, 123, 100

04/01/2018, a, 123, 10

05/01/2018, a, 123, 200

06/01/2018, a, 123, 200

07/01/2018, a, 123, 10

08/01/2018, a, 123, 10

09/01/2018, a, 123, 10

];

NoConcatenate

TempTable:

If(material = Previous(material),

If(cost = Previous(cost), Peek('Flag'), RangeSum(Peek('Flag'), 1)), 1) as Flag

Resident table

Order By store, material;

Drop Table table;

finalTable:

NoConcatenate

material,

cost,

Flag,

Date(Min(date)) as MinDate,

Date(Max(date)) as MaxDate

Resident TempTable

Group By store, material, cost,Flag;

drop Field Flag;

drop Table TempTable;

Result:

4 Replies

## Re: reduce table rows with date_from and date_to columns

Try this,

Data:

Load *,If(Previous(cost) = cost,1,0) as flag inline [

date, store, material, cost

01/01/2018, a, 123, 10

02/01/2018, a, 123, 10

03/01/2018, a, 123, 100

04/01/2018, a, 123, 10

05/01/2018, a, 123, 200

06/01/2018, a, 123, 200

];

Data1:

Load *,if(Previous(flag)=1 and flag=0,1,flag) as flag1

Resident Data order by date desc;

Drop table Data;

Load store as s, material as m, cost as c,Date(min(date)) as startdate, Date(max(date)) as enddate

Resident Data1

group by store,material,cost,flag1;

Regards,

Kaushik Solanki

Contributor

## Re: reduce table rows with date_from and date_to columns

but if you have another cost 10 after 200, it wont work

date, store, material, cost

01/01/2018, a, 123, 10

02/01/2018, a, 123, 10

03/01/2018, a, 123, 100

04/01/2018, a, 123, 10

05/01/2018, a, 123, 200

06/01/2018, a, 123, 200

07/01/2018, a, 123, 10

08/01/2018, a, 123, 10

09/01/2018, a, 123, 10

];

im trying something with autonumber

Esteemed Contributor

## Re: reduce table rows with date_from and date_to columns

Try this:

table:

Load date(date#(date,'DD/MM/YYYY')) as date, store, material, cost Inline [

date, store, material, cost

date, store, material, cost

01/01/2018, a, 123, 10

02/01/2018, a, 123, 10

03/01/2018, a, 123, 100

04/01/2018, a, 123, 10

05/01/2018, a, 123, 200

06/01/2018, a, 123, 200

07/01/2018, a, 123, 10

08/01/2018, a, 123, 10

09/01/2018, a, 123, 10

];

NoConcatenate

TempTable:

If(material = Previous(material),

If(cost = Previous(cost), Peek('Flag'), RangeSum(Peek('Flag'), 1)), 1) as Flag

Resident table

Order By store, material;

Drop Table table;

finalTable:

NoConcatenate

material,

cost,

Flag,

Date(Min(date)) as MinDate,

Date(Max(date)) as MaxDate

Resident TempTable

Group By store, material, cost,Flag;

drop Field Flag;

drop Table TempTable;

Result:

Contributor

life saver!

thanks a lot