topic Re: Splitting Targets in QlikView

Splitting Targets

Thu, 05 Nov 2015 12:20:58 GMT

HI All

I Have a targets table and I want to split the Week target equally between each date

Actual Table

Branch	Week Target	StartDate	EndDate
101	5510.00	03/09/2015	07/09/2015
102	2270.00	03/09/2015	07/09/2015
103	650.00	03/09/2015	07/09/2015
104	1945.00	03/09/2015	07/09/2015
105	1620.00	03/09/2015	07/09/2015
106	2595.00	03/09/2015	07/09/2015
107	1135.00	03/09/2015	07/09/2015

Expected table

Branch	Daily Target	Date	StartDate	EndDate
101	1102.00	03/09/2015	03/09/2015	07/09/2015
101	1102.00	04/09/2015	03/09/2015	07/09/2015
101	1102.00	05/09/2015	03/09/2015	07/09/2015
101	1102.00	06/09/2015	03/09/2015	07/09/2015
101	1102.00	07/09/2015	03/09/2015	07/09/2015

.102

103

Please Suggest how to do this

Re: Splitting Targets

Anonymous — Thu, 05 Nov 2015 12:24:14 GMT

Is it going to be fixed i.e 5 times for each?

Re: Splitting Targets

Clever_Anjos — Thu, 05 Nov 2015 12:27:15 GMT

t:
LOAD Branch,
     [Week Target],
     StartDate,
     EndDate
FROM [https://community.qlik.com/thread/190466]
(html, codepage is 1252, embedded labels, table is @1);


load
  Branch,
  [Week Target],
  Date(StartDate + IterNo() - 1) as Date,
  [Week Target]/5 as [Date Target],
  StartDate,
     EndDate
Resident t
While  IterNo() <=  5;
drop Table t;

Final result

Re: Splitting Targets

Thu, 05 Nov 2015 12:34:54 GMT

HI Balraj

it has to pick up the number of days between star Date and End Date

Re: Splitting Targets

Clever_Anjos — Thu, 05 Nov 2015 12:42:27 GMT

Does your week´s number of days change?

Re: Splitting Targets

andrespa — Thu, 05 Nov 2015 12:46:37 GMT

Hi John, I'm afraid it's not possible to put conditions with join statements. But you can do something like this:

TARGETS:

LOAD Branch,

[Week Target],

StartDate,

EndDate

FROM

(biff, embedded labels, table is Target$);

LEFT JOIN(TARGETS)

SALES:

LOAD Branch,

Sales,

Date

FROM

(biff, embedded labels, table is Sheet1$);

NoConcatenate

FINAL_TABLE:

LOAD * RESIDENT TARGETS

WHERE Date>StartDate AND Date<EndDate;

DROP TABLE FINAL_TABLE;

If I understand your requirement right, this should be one solution. Hope it helps.

Best regards,

Andrés

Re: Splitting Targets

Thu, 05 Nov 2015 12:52:58 GMT

HI Clever Anjos

the number of days between start Date and End Date varies

Most of them have 5 days

but some have only 4

In that case of 5 your script works correctly

but the day targets differs when t is 4 days

Re: Splitting Targets

Clever_Anjos — Thu, 05 Nov 2015 12:57:11 GMT

So, this can solve your needs

t:
LOAD Branch,
     [Week Target],
     StartDate,
     EndDate
FROM [https://community.qlik.com/thread/190466]
(html, codepage is 1252, embedded labels, table is @1);


t2:
load
  Branch,
  [Week Target],
  Date(StartDate + IterNo() - 1) as Date,
  [Week Target]/(EndDate-StartDate+1) as [Date Target],
  StartDate,
    EndDate
Resident t
While  StartDate + IterNo() - 1 <=  EndDate;
drop Table t;

Re: Splitting Targets

Thu, 05 Nov 2015 13:36:34 GMT

HI Clever Anjos

Thanks for your help