topic Data is not coming when date dimensions added in group by in QlikView

Data is not coming when date dimensions added in group by

Mon, 29 Sep 2014 15:47:47 GMT

i am using below code to calculate some measures. but when i add time dimensions ( Year, Month and Date ) into the Code it is giving me error

LOAD Id,

Code,

Month,

Date,

Year_No,

sum(No_Of_Rooms) as No_Of_Rooms,

sum(Count_Sub) as Count_Sub,

sum(Revenue_sub) as Revenue_sub,

sum(Count_Cs) as Count_Cs,

sum(Revenue_Cs) as Revenue_Cs,

sum(Avail_Rooms) as Avail_Rooms,

((sum(Count_Sub)/(No_Of_Rooms))*100) as Occ_Sub,

(sum(Count_Cs)/sum(Avail_Rooms)) as Occ_Cs,

(sum(Revenue_sub)/sum(No_Of_Rooms)) as RevPar_sub,

(sum(Revenue_Cs)/sum(Avail_Rooms)) as RevPar_Cs,

(sum(Revenue_sub)/sum(Count_Sub)) as ADR_Sub,

(sum(Revenue_Cs)/sum(Count_Cs)) as ADR_Cs,

((((sum(Revenue_sub)/sum(No_Of_Rooms))/(sum(Revenue_Cs)/sum(Avail_Rooms)))-1)*100) as RevPAR_Index

FROM

xyz qvd(qvd);

group by Code,Id,Month,Year_No,Date;

Please help

Re: Data is not coming when date dimensions added in group by

Anonymous — Mon, 29 Sep 2014 15:54:41 GMT

Hi Sreeharsha

try below code:

LOAD Id,

Code,

Month,

Date,

Year_No,

sum(No_Of_Rooms) as No_Of_Rooms,

sum(Count_Sub) as Count_Sub,

sum(Revenue_sub) as Revenue_sub,

sum(Count_Cs) as Count_Cs,

sum(Revenue_Cs) as Revenue_Cs,

sum(Avail_Rooms) as Avail_Rooms,

((sum(Count_Sub)/(No_Of_Rooms))*100) as Occ_Sub,

(sum(Count_Cs)/sum(Avail_Rooms)) as Occ_Cs,

(sum(Revenue_sub)/sum(No_Of_Rooms)) as RevPar_sub,

(sum(Revenue_Cs)/sum(Avail_Rooms)) as RevPar_Cs,

(sum(Revenue_sub)/sum(Count_Sub)) as ADR_Sub,

(sum(Revenue_Cs)/sum(Count_Cs)) as ADR_Cs,

((((sum(Revenue_sub)/sum(No_Of_Rooms))/(sum(Revenue_Cs)/sum(Avail_Rooms)))-1)*100) as RevPAR_Index

FROM

xyz qvd(qvd)

group by Code,Id,Month,Year_No,Date;

still if you get error please copy error message in post

Re: Data is not coming when date dimensions added in group by

MarcoWedel — Mon, 29 Sep 2014 16:20:53 GMT

Hi,

one semicolon to much:

try without it ..

hope this helps

regards

Marco

Re: Data is not coming when date dimensions added in group by

Mon, 29 Sep 2014 16:26:37 GMT

The ';' is typo error, i did not use this at script,

the script is running fine but am not getting values

Re: Data is not coming when date dimensions added in group by

Mon, 29 Sep 2014 16:26:44 GMT

The ';' is typo error, i did not use this at script,

the script is running fine but am not getting values

Re: Data is not coming when date dimensions added in group by

Mon, 29 Sep 2014 16:27:10 GMT

The ';' is typo error, i did not use this at script,

the script is running fine but am not getting values

Re: Data is not coming when date dimensions added in group by

Anonymous — Mon, 29 Sep 2014 16:29:25 GMT

Data not coming ?

for which fields?

Re: Data is not coming when date dimensions added in group by

jaimeaguilar — Mon, 29 Sep 2014 16:41:09 GMT

Hi,

Are you sure your data exists at that level of detail (Id,Code,Month,Date,Year_No)?. If your data is not at all levels of detail it may be causing that the script returns you nothing.

One way you can debug this is loading a small portion of data with these dimensions and without the group by. Then in the front-end create a straight table with these dimensions and some/all the expressions you included in the group by script. In order to check that data exists at all levels of detail you should be able to see the correct split of data between each combination of dimension values (eg. Year_No > Month > Date >Code). If you get the same value for a whole dimension it would mean that your data is not splitted for that dimension,

hope this helps

regards

Re: Data is not coming when date dimensions added in group by

maxgro — Mon, 29 Sep 2014 19:41:55 GMT

maybe your query doesn't get any data? remove sum...and group by and try

I think it should return an error because of a missing aggr function (sum)

LOAD Id,

Code,

Month,

Date,

Year_No,

sum(No_Of_Rooms) as No_Of_Rooms,

sum(Count_Sub) as Count_Sub,

sum(Revenue_sub) as Revenue_sub,

sum(Count_Cs) as Count_Cs,

sum(Revenue_Cs) as Revenue_Cs,

sum(Avail_Rooms) as Avail_Rooms,

((sum(Count_Sub)/(No_Of_Rooms))*100) as Occ_Sub, sum(No_Of_Rooms)

(sum(Count_Cs)/sum(Avail_Rooms)) as Occ_Cs,

(sum(Revenue_sub)/sum(No_Of_Rooms)) as RevPar_sub,

(sum(Revenue_Cs)/sum(Avail_Rooms)) as RevPar_Cs,

(sum(Revenue_sub)/sum(Count_Sub)) as ADR_Sub,

(sum(Revenue_Cs)/sum(Count_Cs)) as ADR_Cs,

((((sum(Revenue_sub)/sum(No_Of_Rooms))/(sum(Revenue_Cs)/sum(Avail_Rooms)))-1)*100) as RevPAR_Index

FROM

xyz qvd(qvd); // xyz.qvd (qvd)

group by Code,Id,Month,Year_No,Date;