topic Re: need help in writing sql code into qlikview code in QlikView

need help in writing sql code into qlikview code

Anonymous — Fri, 13 Mar 2015 17:17:36 GMT

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!

Re: need help in writing sql code into qlikview code

sunny_talwar — Fri, 13 Mar 2015 17:29:10 GMT

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

Best,

Re: need help in writing sql code into qlikview code

Anonymous — Fri, 13 Mar 2015 17:33:51 GMT

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!

Re: need help in writing sql code into qlikview code

sunny_talwar — Fri, 13 Mar 2015 17:36:40 GMT

Got it. Let me work on a sample.

Best,

Re: need help in writing sql code into qlikview code

Anonymous — Fri, 13 Mar 2015 17:42:13 GMT

Sure! I really appreciate your quick reply!

Thanks!

Re: need help in writing sql code into qlikview code

maxgro — Fri, 13 Mar 2015 18:23:43 GMT

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;

Re: need help in writing sql code into qlikview code

Anonymous — Fri, 13 Mar 2015 18:31:35 GMT

Can we use for loop in this situation?

Re: need help in writing sql code into qlikview code

maxgro — Fri, 13 Mar 2015 19:04:08 GMT

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;

DROP Table source;

Re: need help in writing sql code into qlikview code

Anonymous — Fri, 13 Mar 2015 20:28:19 GMT

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!