Aggregation at Script Level

[naziralala]

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

[SreeniJD]

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

[naziralala]

Hi Jd,

Can you please explain in more detail?

thanks

[Digvijay_Singh]

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.

[SreeniJD]

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

[Anonymous]

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?

[avinashelite]

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

[naziralala]

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.

[naziralala]

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.

[avinashelite]

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;