need help in writing sql code into qlikview code

[Anonymous]

Can someone please help me writing this piece of code in qlikview..

vmin = min(checkindate)

vmax= max(Checkindate)

for dates min(checkindate) to max(check in date)

{

for i=1 to duration ---duration = peek(table,0,duration)

{

      v_check_in_date: = vmin+i-1;

      v_sales = peek(tabe,i-1,sales);

Temp_table:

      Load OR REPLACE

      v_check_in_date as v_temp1,

      v_sales as v_sales

      from v_check_in_date,v_Sales;

}

}

final_table:

select check_in_date,sum(sales) from temp_table group by check_in_date;

Thanks!

[sunny_talwar]

Can you elaborate on what you are trying to achieve here? Just looking at the code here is kind of confusing.

Best,

S

[Anonymous]

I have 3 fields in a table , start date,duration & sales.

And I calculated the end date.

------------------------------------------------------------------------------------

start date    duration           sales         end date = startdate+duration

-----------------------------------------------------------------------------------

1/1/15            3                       1                           1/4/15

1/2/15            4                       2                           1/6/15

1/3/15            6                       1                           1/9/15

----------------------------------------------------------------------------------------

If i select 1/3/15 it should display all the dates till the end date(  1/3/15 ,1/4/15,1/5/15,1/6/15,1/7/15,1/8/15 )with the corresponding  sum(sales) for each particular day,like below:

startdate  1/1/15     1/2/15   1/3/15      1/4/15        1/5/15    1/6/15     1/7/15      1/8/15    

sales         1                1         1

                                   2         2           2               2

                                              1           1               1              1          1                 1          

sum(sales)   1              3         4            3               3              1          1                 1

startdate       sales

----------------------------------------

1/3                 4

1/4                 3

1/5                 3

1/6                 1

1/7                 1

1/8                 1

Thanks!

[sunny_talwar]

Got it. Let me work on a sample.

Best,

S

[Anonymous]

Sure! I really appreciate your quick reply!

Thanks!

[maxgro]

PFA

SET DateFormat='M/D/YY';

source:

load rowno() as id, * inline [

start,    duration,           sales,         end

1/1/15     ,       3   ,                    1 ,                          1/4/15

1/2/15      ,      4    ,                   2  ,                         1/6/15

1/3/15       ,     6     ,                  1   ,                        1/9/15

];

final:

NoConcatenate load

  id,

  date(start +iterno()-1) as start,

  end,

  duration,

  sales

Resident

  source

While

  IterNo()<=duration;

DROP Table source;

[Anonymous]

Can we use for loop in this situation?

[maxgro]

maybe but it seems more difficult, I try

SET DateFormat='M/D/YY';

source:

load rowno() as id, * inline [

start,    duration,          sales,        end

1/1/15    ,      3  ,                    1 ,                          1/4/15

1/2/15      ,      4    ,                  2  ,                        1/6/15

1/3/15      ,    6    ,                  1  ,                        1/9/15

];

load min(start) as minstart, max(start) as maxstart resident source;

vstart = Peek('minstart');

vend = Peek('maxstart');

final: load null() AutoGenerate 1;

for i=$(vstart) to $(vend)

  a: load min(duration) as minduration resident source where num(start)=$(i);

  vduration = Peek('minduration');

  DROP Table a;

  for j=1 to $(vduration)

  Concatenate (final) load $(i) as id, date(start+$(j)-1) as start, duration, sales Resident source where num(start)=$(i);

  NEXT;

NEXT;

DROP Table source;

[Anonymous]

Its taking for ever to load the data.

I tried while loop, but the problem is when i use 'group by' am not getting the expected result

when i select 1/3/15   i see 1/2/15, 2 in the dates stay and sales resp.

But the expected result is

1/3                 4

1/4                 3

1/5                 3

1/6                 1

1/7                 1

1/8                 1

Below is my script, can you please help me get this right.

Load

     Gateway,

     Destination, 

     Region,

     Locale, 

     [Resort Name],

     [Hotel Chain],

     [Room Category],

     [Travel Mode],

     [Travel Date],

     [Travel Quarter],

     [Travel Year],

     [Travel Month],

     [Travel Week],

     [Booking Date], 

     [Booking Year],

     [Booking Quarter],

     [Booking Month],

     [Booking Week],

     [Hotel Vendor ID],

     [Hotel Vendor],     

     [Hotel Revenue],

     [Number of Rooms],

     sum([Number of Rooms]) as [Rooms per Night],

     date([Travel Date] + IterNo() -1) as [Dates Stay]

Resident CCData

while(IterNo()  <= ([CheckOut Date]-[Travel Date]))

group by

     Gateway,

     Destination, 

     Region,

     Locale, 

     [Resort Name],

     [Hotel Chain],

     [Room Category],

     [Travel Mode],

     [Travel Date],

     [Travel Quarter],

     [Travel Year],

     [Travel Month],

     [Travel Week],

     [Booking Date], 

     [Booking Year],

     [Booking Quarter],

     [Booking Month],

     [Booking Week],

     [Hotel Vendor ID],

     [Hotel Vendor],     

     [Hotel Revenue],

     [Number of Rooms];

Thanks!