Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
arsallee3
New Contributor III

Do while loop

Hello all,

I am struggling with creating a table with all dates listed from another table. The original has a project ID, Start date, end date and projection amount. Because the start and end dates can span across months I am looking to be able to get projection for a given month/Week etc. based upon total projection amount divided by days of project.

Example:

ProjectId, StartDate, EndDate, Projection

1, 8/29/2016, 9/2/2016, 150,000

2, 9/1/2016, 9/05/2016, 100,000

Each project has 5days of work so daily projection would be 15,000 & 10,000 respectively.

Would like table to be created as follows:

ProjectId, Date, Projection

1, 08/29/2016, 15000

1, 08/30/2016, 15000

1, 08/31/2016, 15000

1, 09/01/2016, 15000

1, 09/02/2016, 15000

2, 09/01/2016, 10000

2, 09/02/2016, 10000

2, 09/03/2016, 10000

2, 09/04/2016, 10000

2, 09/05/2016, 10000

Then would be able to sum projection by month

Example August would be 45,000 for both projects and September would be 205,000 for both projects and total would be 250,000 across both months. Below is code I have to start of course not working. Would appreciate if see better way to do this or what I have incorrect?

Thanks

//Create table for daily projection volumes

Let vJobStartDate = Today();
Let vJobEndDate = Today();
Let vServiceDays = 0;


Let vNoOfRows = NoOfRows('ProjectMaster');   
//Get the values from each row and create a daily projection table
For i=1 to $(vNoOfRows)-1
set vProjectId = Peek('ProjectId',$(i),'ProjectMaster');
set vJobStartDate = Peek('StartDate',$(i),'ProjectMaster');
    set vJobEndDate = Peek('EndDate',$(i),'ProjectMaster');
    set vServiceDays = Peek('EndDate',$(i),'ProjectMaster') - Peek('StartDate',$(i),'ProjectMaster');
    set vDailyProjection = Peek('ProjectionTest',$(i),'ProjectMaster')/$(vServiceDays);

// This script loops through each ProjectId and creates a table with the date and daily amounts for each date

    Set a=1;
    Do while a < vServiceDays
   
    ProjectionTable:
Load if(isnull($(vProjectId)),'NULL'&$(i),$(vProjectId)) as ProjectId,
  if(isnull($(vJobStartDate)),'NULL'&$(i),$(vJobStartDate)) as JobProjectionDate,
     if(isnull($(vDailyProjection)),'NULL'&$(i),$(vDailyProjection))as DailyProjection;
    Let a=a+1;
    Let vJobStartDate =vJobStartDate+1;
    Loop
   
   
Next i;

Tags (2)
1 Solution

Accepted Solutions
arsallee3
New Contributor III

Re: Do while loop

Gysbert,

This was perfect except I needed to count weekends as well so modified that calculation a little bit.

TempProjectionTable:

Load

  ProjectId,

  StartDate,

    EndDate,

  Projection

Resident ProjectMaster

// Exclude the Company, Admin & Training Clients

where ClientId <> 168 and

  ClientId <> 214 and

    ClientId <> 237;

ProjectionTable:

Load

  ProjectId,

    Num(StartDate + IterNo() -1) as JobProjectionDate,

    Round(Projection/(Num(EndDate)-Num(StartDate)+1),0.00001) as ProjectionAmount

Resident TempProjectionTable

While IterNo() <= EndDate - StartDate +1

Order by StartDate asc;

Drop Table TempProjectionTable;

View solution in original post

4 Replies
MVP & Luminary
MVP & Luminary

Re: Do while loop

Perhaps something like this:

LOAD

     ProjectID,

     StartDate + IterNo() -1 as Date,

     Date(MonthStart(StartDate + IterNo() -1),'MMM-YYYY') as MonthYear,

     Projection/NetWorkDays(StartDate,EndDate) as Projection

WHILE

     StartDate + IterNo() -1 <= EndDate

     ;

LOAD * INLINE [

ProjectId, StartDate, EndDate, Projection

1, 8/29/2016, 9/2/2016, 150,000

2, 9/1/2016, 9/05/2016, 100,000

];


talk is cheap, supply exceeds demand
Highlighted

Re: Do while loop

Try to avoid posting duplicate threads:

For Next/Do While loop

arsallee3
New Contributor III

Re: Do while loop

Yes, I am sorry, I thought I did something incorrect as the first one did not get a moderation email back until about 24 hours later and that was just unusual.

arsallee3
New Contributor III

Re: Do while loop

Gysbert,

This was perfect except I needed to count weekends as well so modified that calculation a little bit.

TempProjectionTable:

Load

  ProjectId,

  StartDate,

    EndDate,

  Projection

Resident ProjectMaster

// Exclude the Company, Admin & Training Clients

where ClientId <> 168 and

  ClientId <> 214 and

    ClientId <> 237;

ProjectionTable:

Load

  ProjectId,

    Num(StartDate + IterNo() -1) as JobProjectionDate,

    Round(Projection/(Num(EndDate)-Num(StartDate)+1),0.00001) as ProjectionAmount

Resident TempProjectionTable

While IterNo() <= EndDate - StartDate +1

Order by StartDate asc;

Drop Table TempProjectionTable;

View solution in original post