topic masster calender in QlikView

masster calender

Wed, 13 May 2015 11:51:51 GMT

here iam getting some error

my sample data below shown.

i taken two tables if i conncated two tables not get error but i dont want concate two tables

tab1:

LOAD id,name,sal,Date(Date#(doj,'DD-MM-YYYY')) as doj Inline [

id,name,sal,doj

1,sri,5000,12-12-2010

2,ram,6000,12-12-2011

3,sati,7000,12-12-2012

4,vam,8000,12-12-2013

5,kittu,9000,12-12-2014];

tab2:

LOAD * Inline [

sno,dept,id

1,hr,8

2,fin,9

3,sal,1

];

sort:

LOAD * Resident tab1 Order by doj desc;

LET vmax=Num(Peek('doj',0,'sort'));

LET vmin=Num(Peek('doj',-1,'sort'));

temp:

LOAD Date($(vmin)+RecNo()-1) as tempdate

AutoGenerate($(vmax)-$(vmin)+1);

cal:

LOAD tempdate as doj,

Year(tempdate) as year,

Month(tempdate) as month,

'Q'&Ceil(Month(tempdate)/3) as qtr

Resident temp;

DROP Tables temp;

Re: masster calender

fvelascog72 — Wed, 13 May 2015 12:04:55 GMT

Hi,

Try with this:

tab1:

LOAD id,name,sal,Date(Date#(doj,'DD-MM-YYYY')) as doj Inline [

id,name,sal,doj

1,sri,5000,12-12-2010

2,ram,6000,12-12-2011

3,sati,7000,12-12-2012

4,vam,8000,12-12-2013

5,kittu,9000,12-12-2014];

tab2:

LOAD * Inline [

sno,dept,id

1,hr,8

2,fin,9

3,sal,1

];

Dates:

LOAD

Max(doj) as "MaxDate",

Min(doj) as "MinDate"

Resident tab1;

LET vmax = Peek('MaxDate',0,'Dates');

LET vmin = Peek('MinDate',0,'Dates');

Drop Table Dates;

temp:

LOAD Date($(vmin)+RecNo()-1) as tempdate

AutoGenerate($(vmax)-$(vmin)+1);

cal:

LOAD tempdate as doj,

Year(tempdate) as year,

Month(tempdate) as month,

'Q'&Ceil(Month(tempdate)/3) as qtr

Resident temp;

DROP Tables temp;

Re: masster calender

Wed, 13 May 2015 12:14:33 GMT

hello

radhakrushna

try this one

tab:

LOAD id,name,sal,Date(Date#(doj,'DD-MM-YYYY')) as doj Inline [

id,name,sal,doj

1,sri,5000,12-12-2010

2,ram,6000,12-12-2011

1,sati,7000,12-12-2012

1,vam,8000,12-12-2013

1,kittu,9000,12-12-2014

];

//Concatenate

tab2:

LOAD * Inline [

id,prod,sales

1,a,500

2,b,600

3,c,700

4,d,750

5,a,100

6,b,100];

sort:

NoConcatenate

LOAD * Resident tab Order By doj;

DROP Table tab;

LET max=Floor(Peek('doj',-1,'sort'));

LET min=Floor(Peek('doj',0,'sort'));

temp:

LOAD Date($(min)+RecNo()-1) as tempdate

AutoGenerate($(max)-$(min)+1);

cal:

LOAD Date(tempdate,'DD-MM-YYYY') as doj,

Year(tempdate) as year,

Month(tempdate) as month,

'Q'&Ceil(Month(tempdate)/3) as qtr

Resident temp;

DROP Tables temp;

Re: masster calender

Wed, 13 May 2015 13:19:41 GMT

what i am done wrong

Re: masster calender

maxgro — Wed, 13 May 2015 15:04:57 GMT

Bold=modified, result in image

tab1:

LOAD id,name,sal,Date(Date#(doj,'DD-MM-YYYY')) as doj Inline [

id,name,sal,doj

1,sri,5000,12-12-2010

2,ram,6000,12-12-2011

3,sati,7000,12-12-2012

4,vam,8000,12-12-2013

5,kittu,9000,12-12-2014];

tab2:

LOAD * Inline [

sno,dept,id

1,hr,8

2,fin,9

3,sal,1

];

sort: NoConcatenate LOAD * Resident tab1 Order by doj desc;

LET vmax=Num(Peek('doj',0,'sort'));

LET vmin=Num(Peek('doj',-1,'sort'));

DROP Table sort;

temp:

LOAD Date($(vmin)+RecNo()-1) as tempdate

AutoGenerate($(vmax)-$(vmin)+1);

cal:

LOAD tempdate as doj,

Year(tempdate) as year,

Month(tempdate) as month,

'Q'&Ceil(Month(tempdate)/3) as qtr

Resident temp;

DROP Tables temp;