Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Can you elaborate on what you are trying to achieve here? Just looking at the code here is kind of confusing.
Best,
S
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!
Got it. Let me work on a sample.
Best,
S
Sure! I really appreciate your quick reply!
Thanks!
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;
Can we use for loop in this situation?
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;
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!