# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Contributor III

## need help in writing sql code into qlikview code

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:

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!

8 Replies
MVP

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

Best,

S

Contributor III
Author

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!

MVP

Got it. Let me work on a sample.

Best,

S

Contributor III
Author

Thanks!

MVP

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:

id,

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

end,

duration,

sales

Resident

source

While

IterNo()<=duration;

DROP Table source;

Contributor III
Author

Can we use for loop in this situation?

MVP

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');

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;

Contributor III
Author

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

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!

Community Browser