Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
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

   

CollectionGroupPeriod_FromPeriod_To
C1G101.10.201501.03.2016
C1G201.10.201501.03.2016
C2G101.01.201501.03.2015
C2G201.01.201501.03.2015

Table 2

SubPeriodPeriod ShareRate
SP110%3%
SP220%10%
SP335%20%
SP420%10%
SP515%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:

    

CollectionGroupDate of PeriodPeriod_FromPeriod_To
C1G101.10.201501.10.201515.10.2015
C1G102.10.201501.10.201515.10.2015
C1G103.10.201501.10.201515.10.2015
C1G104.10.201501.10.201515.10.2015
C1G105.10.201501.10.201515.10.2015
C1G106.10.201501.10.201515.10.2015
C1G107.10.201501.10.201515.10.2015
C1G108.10.201501.10.201515.10.2015
C1G109.10.201501.10.201515.10.2015
C1G110.10.201501.10.201515.10.2015
C1G111.10.201501.10.201515.10.2015
C1G112.10.201501.10.201515.10.2015
C1G113.10.201501.10.201515.10.2015
C1G114.10.201501.10.201515.10.2015
C1G115.10.201501.10.201515.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:

        

CollectionGroupDate of PeriodSubPeriodPeriod ShareSubPeriod Date FromSubPeriod Date ToRateRate by Day
C1G101.10.2015SP110%01.10.201515.10.20153%0,002
C1G102.10.2015SP110%01.10.201515.10.20153%0,002
C1G103.10.2015SP110%01.10.201515.10.20153%0,002
C1G104.10.2015SP110%01.10.201515.10.20153%0,002
C1G105.10.2015SP110%01.10.201515.10.20153%0,002
C1G106.10.2015SP110%01.10.201515.10.20153%0,002
C1G107.10.2015SP110%01.10.201515.10.20153%0,002
C1G108.10.2015SP110%01.10.201515.10.20153%0,002
C1G109.10.2015SP110%01.10.201515.10.20153%0,002
C1G110.10.2015SP110%01.10.201515.10.20153%0,002
C1G111.10.2015SP110%01.10.201515.10.20153%0,002
C1G112.10.2015SP110%01.10.201515.10.20153%0,002
C1G113.10.2015SP110%01.10.201515.10.20153%0,002
C1G114.10.2015SP110%01.10.201515.10.20153%0,002
C1G115.10.2015SP110%01.10.201515.10.20153%0,002
C1G116.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G117.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G118.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G119.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G120.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G121.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G122.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G123.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G124.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G125.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G126.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G127.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G128.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G129.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G130.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G131.10.2015SP220%16.10.201510.11.201510%0,003846154
C1G101.11.2015SP220%16.10.201510.11.201510%0,003846154
C1G102.11.2015SP220%16.10.201510.11.201510%0,003846154
C1G103.11.2015SP220%16.10.201510.11.201510%0,003846154
C1G104.11.2015SP220%16.10.201510.11.201510%0,003846154
C1G105.11.2015SP220%16.10.201510.11.201510%0,003846154
C1G106.11.2015SP220%16.10.201510.11.201510%0,003846154
C1G107.11.2015SP220%16.10.201510.11.201510%0,003846154
C1G108.11.2015SP220%16.10.201510.11.201510%0,003846154
C1G109.11.2015SP220%16.10.201510.11.201510%0,003846154
C1G110.11.2015SP220%16.10.201510.11.201510%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
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;

View solution in original post

4 Replies
MVP
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
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;

View solution in original post

Hi,

another solution could be:

QlikCommunity_Thread_167242_Pic1.JPG

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
Creator II

Swuehl, Marko thanks a lot for yours help.

Both solutions are working fine