Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for
Search instead for
Did you mean:
Creator II

## Percents of Time Period or Magic of Transformation

Hi friends.

The main purpose is to join 2 tables based on specific rules.

Table 1

 Collection Group Period_From Period_To C1 G1 01.10.2015 01.03.2016 C1 G2 01.10.2015 01.03.2016 C2 G1 01.01.2015 01.03.2015 C2 G2 01.01.2015 01.03.2015

Table 2

 SubPeriod Period Share Rate SP1 10% 3% SP2 20% 10% SP3 35% 20% SP4 20% 10% SP5 15% 15%

Collection and Group - it's some attributes of product.

Period From and Period To - Range (Life Cycle) of Dates, to which is necessary to join a Rate.

Period Share - it's percent of Range.

Rate - it's some KPI.

For instance for the range 01.10.2015 - 01.03.2016 first SubPeriod will be 01.10.2015-15.10.2015 which equals 10% of range, second SubPeriod will be 16.10.2015-10.11.2015 which equals 20% of range and so on.

It means that i need to divide the period into subperiods according to percent.

Please note that key fields in the join between the two tables aren't present.

What i'm doing:

Step One: Make single date from two dates. Table - Table 1, Fields - Period From and Period To.

```tmp_1:
First 1
LOAD
Collection,
Group,
Period_From,
Period_To
FROM
DataSource.xlsx
(ooxml, embedded labels, table is Sh1)
Where Not IsNull(Collection);

tmp_2:
LOAD
Collection,
Group,
Date(Period_From+IterNo()-1) as [Date of Period],
Period_From,
Period_To
Resident tmp_1
While Date(Period_From+IterNo()-1)<=Period_To
Order by
Collection,
Group;

DROP Table tmp_1;

```

As the result we've got the next table:

 Collection Group Date of Period Period_From Period_To C1 G1 01.10.2015 01.10.2015 15.10.2015 C1 G1 02.10.2015 01.10.2015 15.10.2015 C1 G1 03.10.2015 01.10.2015 15.10.2015 C1 G1 04.10.2015 01.10.2015 15.10.2015 C1 G1 05.10.2015 01.10.2015 15.10.2015 C1 G1 06.10.2015 01.10.2015 15.10.2015 C1 G1 07.10.2015 01.10.2015 15.10.2015 C1 G1 08.10.2015 01.10.2015 15.10.2015 C1 G1 09.10.2015 01.10.2015 15.10.2015 C1 G1 10.10.2015 01.10.2015 15.10.2015 C1 G1 11.10.2015 01.10.2015 15.10.2015 C1 G1 12.10.2015 01.10.2015 15.10.2015 C1 G1 13.10.2015 01.10.2015 15.10.2015 C1 G1 14.10.2015 01.10.2015 15.10.2015 C1 G1 15.10.2015 01.10.2015 15.10.2015

and so on...

Step Two: Omg...How to Join ? Any Ideas?

First life cycle (range) have got 152 days. We have the field Period From.

May be i should use cycle "For Next" for each row of Table 1 to join Table 2 ?

May be something like:

SubPeriod From = 0% * {152} + Num(01.10.2015)

SubPeriod Till = 10% * {152} + Num(01.10.2015).

???

Final table should looks like:

 Collection Group Date of Period SubPeriod Period Share SubPeriod Date From SubPeriod Date To Rate Rate by Day C1 G1 01.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 02.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 03.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 04.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 05.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 06.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 07.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 08.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 09.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 10.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 11.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 12.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 13.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 14.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 15.10.2015 SP1 10% 01.10.2015 15.10.2015 3% 0,002 C1 G1 16.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 17.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 18.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 19.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 20.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 21.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 22.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 23.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 24.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 25.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 26.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 27.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 28.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 29.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 30.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 31.10.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 01.11.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 02.11.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 03.11.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 04.11.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 05.11.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 06.11.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 07.11.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 08.11.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 09.11.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154 C1 G1 10.11.2015 SP2 20% 16.10.2015 10.11.2015 10% 0,003846154

I hope I clearly explained the essence of the problem.

Friends please help.

Any recommendations, advices, logic, peace of code, scripts, ideas...

Thanks in advance.

P.S.: Data source and peace of script in attachment.

1 Solution

Accepted Solutions
MVP

There was an issue with detecting a change in Collection / Group, I also added a Date rounding.

```tmp_1:
First 4
LOAD
Collection,
Group,
autonumber(Collection & Group) as Key,
Period_From,
Period_To
FROM
DataSource.xlsx
(ooxml, embedded labels, table is Sh1)
Where Not IsNull(Collection);

JOIN
LOAD
SubPeriod,
[Period Share],
Rate,
rangesum(peek(Acc),[Period Share]) as Acc

FROM
[DataSource.xlsx]
(ooxml, embedded labels, table is Sh1)
Where Not IsNull(SubPeriod);

SubPeriods:
LOAD *,
if(Key=Peek(Key), Date(Peek(Period_Sub_To)+1), Period_From) as Period_Sub_From,
Date(round(rangesum( (Period_To-Period_From)*Acc,Period_From))) as Period_Sub_To
RESIDENT tmp_1;

drop table tmp_1;
```
4 Replies
MVP

This will create a join of the sub periods to your table with Collection and Group and create the sub periods.

tmp_1:

First 1

LOAD

Collection,

Group,

Period_From,

Period_To

FROM

DataSource.xlsx

(ooxml, embedded labels, table is Sh1)

Where Not IsNull(Collection);

JOIN

LOAD

SubPeriod,

[Period Share],

Rate,

rangesum(peek(Acc),[Period Share]) as Acc

FROM

[DataSource.xlsx]

(ooxml, embedded labels, table is Sh1)

Where Not IsNull(SubPeriod);

SubPeriods:

LOAD *,

Timestamp(rangesum( (Period_To-Period_From)*Peek(Acc),Period_From)) as Period_Sub_From,

Timestamp(rangesum( (Period_To-Period_From)*Acc,Period_From)) as Period_Sub_To

RESIDENT tmp_1;

drop table tmp_1;

The resulting table looks like

Collection Group Period_From Period_To Period_Sub_From Period_Sub_To SubPeriod Period Share Rate Acc
C1G101.10.201501.03.201601.10.2015 0:00:0016.10.2015 4:48:00SP110%3%0,1
C1G101.10.201501.03.201616.10.2015 4:48:0015.11.2015 14:24:00SP220%10%0,3
C1G101.10.201501.03.201615.11.2015 14:24:0007.01.2016 19:12:00SP335%20%0,65
C1G101.10.201501.03.201607.01.2016 19:12:0007.02.2016 4:48:00SP420%10%0,85
C1G101.10.201501.03.201607.02.2016 4:48:0001.03.2016 0:00:00SP515%15%1

You'll need to decide how to transform the timestamps to dates (by rounding, floor / ceil etc.) not sure what your business logic is here.

From here, you can proceed with your logic to generate single dates, right?

MVP

There was an issue with detecting a change in Collection / Group, I also added a Date rounding.

```tmp_1:
First 4
LOAD
Collection,
Group,
autonumber(Collection & Group) as Key,
Period_From,
Period_To
FROM
DataSource.xlsx
(ooxml, embedded labels, table is Sh1)
Where Not IsNull(Collection);

JOIN
LOAD
SubPeriod,
[Period Share],
Rate,
rangesum(peek(Acc),[Period Share]) as Acc

FROM
[DataSource.xlsx]
(ooxml, embedded labels, table is Sh1)
Where Not IsNull(SubPeriod);

SubPeriods:
LOAD *,
if(Key=Peek(Key), Date(Peek(Period_Sub_To)+1), Period_From) as Period_Sub_From,
Date(round(rangesum( (Period_To-Period_From)*Acc,Period_From))) as Period_Sub_To
RESIDENT tmp_1;

drop table tmp_1;
```
MVP

Hi,

another solution could be:

```tabInput:
LOAD *, RecNo() as ID1, Period_To-Period_From as PeriodLength FROM [https://community.qlik.com/thread/167242] (html, codepage is 1252, embedded labels, table is @1);
Join
LOAD *, RangeSum([Period Share],Peek(PeriodShareSum)) as PeriodShareSum
FROM [https://community.qlik.com/thread/167242] (html, codepage is 1252, embedded labels, table is @2);

tabResult:
LOAD *,
DayName(SubPeriod_From+IterNo()-1) as [Date of Period]
While SubPeriod_From+IterNo()-1 <= SubPeriod_To;
LOAD Collection,
Group,
SubPeriod,
[Period Share],
DayName(If(ID1=Previous(ID1),Previous(PeriodShareSum)*PeriodLength,0)+Period_From) as SubPeriod_From,
DayName(PeriodShareSum*PeriodLength+Period_From) as SubPeriod_To
Resident tabInput;

DROP Table tabInput;
```

hope this helps

regards

Marco

Creator II
Author

Swuehl, Marko thanks a lot for yours help.

Both solutions are working fine

Community Browser