topic Re: Aggregation at Script Level in QlikView

Aggregation at Script Level

naziralala — Fri, 16 Oct 2015 02:48:22 GMT

Hello All,

I am trying to extract the data from qvd and store in another qvd as below:

Country, city, week, year1, year2 and year3 are the labels or column names in output qvd and example set of data is below:

Mineapolis,calcutta,23,45,6,56

45 is total no of bookings for year 2015, 6 is total no of bookings in 2014 and 56 is total no of bookings in 2013 for that week - aggr of bookings per day/year /week

Can someone assist asap.

Thanks in advance..

Nazira

Re: Aggregation at Script Level

SreeniJD — Fri, 16 Oct 2015 02:56:31 GMT

Nizira,

Are you looking for aggregation of bookings by year, previous year, previous to previous year?

Use the below functions..

YearStart(Max(Date)) and YearEnd(Max(Date))

HTH

Sreeni

Re: Aggregation at Script Level

naziralala — Fri, 16 Oct 2015 02:59:09 GMT

Hi Jd,

Can you please explain in more detail?

thanks

Re: Aggregation at Script Level

Digvijay_Singh — Fri, 16 Oct 2015 03:34:49 GMT

Can you share structure of input QVD, if it is having same structure but data at more granular level like for each day, then you may need to use sum(booking nos) along with 'group by week' while loading second qvd. Need to understand better by the structure of the source data.

Re: Aggregation at Script Level

SreeniJD — Fri, 16 Oct 2015 04:07:55 GMT

Hi Nazira-

As suggested by Digvijay‌ can you please share the structure of input QVD, as this will help us to provide suitable solution.

Sreeni

Re: Aggregation at Script Level

Anonymous — Fri, 16 Oct 2015 05:52:25 GMT

Hi Nazirala,

Please post the structure of input QVD and elaborate on :

45 is total no of bookings for year 2015, 6 is total no of bookings in 2014 and 56 is total no of bookings in 2013 for that week - aggr of bookings per day/year /week.

1.Is the count of bookings for said years is for whole year or for particular week in that year?

2.Do you want aggregation for day/week/year?

Re: Aggregation at Script Level

avinashelite — Fri, 16 Oct 2015 05:58:52 GMT

You can make use of the GRoup by clause in order to calculate the count required.

Try like this :
LOAD count(ID) as Booking_Count,

year

from A.qvd(qvd)

group by year ;

This is an example to get the count according to the years, similarly you can get the count for rest

Re: Aggregation at Script Level

naziralala — Mon, 19 Oct 2015 00:10:37 GMT

Here is my script.. as below:

[Temp CC Data Output Aggr]:

Load Region as Country,

[City Code] as CCode,

[Check week] as [Check-in week],

//[Booking Year] as BkYear,

//[Booking ID] as BKID,

Region&'-'&[City Code]&'-'&[Check week] as Key,

Count([Booking ID]) as [Year($(vCurrYear))]

Resident [Tmp CC Data Output]

Where [Booking Year]=$(vCurrYear)

Group By Region,

[City Code],//[Booking Year],

[Check week]

Order By Region asc,

[City Code] asc,

//[Booking Year] desc,

[Check week] asc;

// ==================================================================================================================================================================================

// Loop to calculate the aggregated Year on Year data

// ==================================================================================================================================================================================

Let vCurrYear=($(vCurrYear)-1);

Do while vCurrYear>=vYearToLoad

Left Join

Load Region as Country,

[City Code] as CCode,

[Check week] as [Check-in week],

//[Booking Year] as BkYear,

//[Booking ID] as BKID,

Region&'-'&[City Code]&'-'&[Check week] as Key,

Count([Booking ID]) as [Year($(vCurrYear))]

Resident [Tmp CC Data Output]

Where [Booking Year]=($(vCurrYear))

Group By Region,

[City Code],//[Booking Year],

[Check week]

Order By Region asc,

[City Code] asc,

//[Booking Year] desc,

[Check week] asc;

Let vCurrYear=($(vCurrYear)-1);

loop;

STORE *

From [Temp CC Data Output Aggr] INTO [CC Data Output Aggr].qvd(qvd);

Now I am stuck with Key field.

I need the Key removed to store into qvd..i.e. my QVD requires all the fields like Country, CCode, [Check week], Year(2015), Year(2014), Year(2013), which I have in my table. But, if I use loop, it is giving me only fields like Country, CCode, [Check week], Year(2015)

Please suggest a way to solve this.

Thanks.

Re: Aggregation at Script Level

naziralala — Mon, 19 Oct 2015 00:11:43 GMT

Here is my script.. as below:

[Temp CC Data Output Aggr]:

Load Region as Country,

[City Code] as CCode,

[Check week] as [Check-in week],

//[Booking Year] as BkYear,

//[Booking ID] as BKID,

Region&'-'&[City Code]&'-'&[Check week] as Key,

Count([Booking ID]) as [Year($(vCurrYear))]

Resident [Tmp CC Data Output]

Where [Booking Year]=$(vCurrYear)

Group By Region,

[City Code],//[Booking Year],

[Check week]

Order By Region asc,

[City Code] asc,

//[Booking Year] desc,

[Check week] asc;

// ==================================================================================================================================================================================

// Loop to calculate the aggregated Year on Year data

// ==================================================================================================================================================================================

Let vCurrYear=($(vCurrYear)-1);

Do while vCurrYear>=vYearToLoad

Left Join

Load Region as Country,

[City Code] as CCode,

[Check week] as [Check-in week],

//[Booking Year] as BkYear,

//[Booking ID] as BKID,

Region&'-'&[City Code]&'-'&[Check week] as Key,

Count([Booking ID]) as [Year($(vCurrYear))]

Resident [Tmp CC Data Output]

Where [Booking Year]=($(vCurrYear))

Group By Region,

[City Code],//[Booking Year],

[Check week]

Order By Region asc,

[City Code] asc,

//[Booking Year] desc,

[Check week] asc;

Let vCurrYear=($(vCurrYear)-1);

loop;

STORE *

From [Temp CC Data Output Aggr] INTO [CC Data Output Aggr].qvd(qvd);

Now I am stuck with Key field.

Please suggest a way to solve this.

Thanks.

Re: Aggregation at Script Level

avinashelite — Mon, 19 Oct 2015 05:23:08 GMT

try like this :this will give the count according to year the current year , if you need according to the year wise UN comment the [Booking Year] column

[Temp CC Data Output Aggr]:

Load Region as Country,

[City Code] as CCode,

//[Booking Year] ,

[Check week] as [Check-in week],

Region&'-'&[City Code]&'-'&[Check week] as Key,

Count([Booking ID]) as [Year($(vCurrYear))]

Resident [Tmp CC Data Output]

Where [Booking Year]=$(vCurrYear)

Group By Region,

[City Code],

//[Booking Year] ,

[Check week]

Order By Region asc,

[City Code] asc,

//[Booking Year] desc,

[Check week] asc;