loading multiple cross table

[Anonymous]

Hello

I need to load multiple cross tables. Can anybody please help how this can be done?

I need the Oct,Nov, Dec, Jan to appear in a column as 'Month' with 'Hours' as separate column. Correspoding HCM effort needs to be populated for that particular month in a third column. See image jpg

I have attached the sample data in Data.xlsx

[its_anandrjs]

You did not attached any sample data please check.

Regards,

Anand

[Anonymous]

Files attached. Thse

[Anonymous]

Files attached

[its_anandrjs]

Hi,

Load your data two times with cross table

HOURS:

CrossTable(HOURS, Data, 19)

LOAD [Approved BP ID],

     [Billable Product],

     [Billable Project],

     [Project Name],

     Proj_ID,

     [Project Type],

     Res_ID,

     [Functional Manager Name],

     [Cost Centre ID],

     [Cost Centre Descr],

     [Employment Category Description],

     [Billable Resource?],

     [Resource Active?],

     PRIMARY_ROLE,

     PRIMARY_SKILL,

     EmpFTE,

     Conv_Factor,

     USDRate,

     Type,

     [Oct-Hours],

     [Nov-Hours],

     [Dec-Hours],

     [Jan-Hours]

FROM

(ooxml, embedded labels, table is [qProjWorkloads_2 (2)]);

STORE HOURS into HOURS.qvd;

DROP Table HOURS;

Effort:

CrossTable(Effort, Data, 19)

LOAD [Approved BP ID],

     [Billable Product],

     [Billable Project],

     [Project Name],

     Proj_ID,

     [Project Type],

     Res_ID,

     [Functional Manager Name],

     [Cost Centre ID],

     [Cost Centre Descr],

     [Employment Category Description],

     [Billable Resource?],

     [Resource Active?],

     PRIMARY_ROLE,

     PRIMARY_SKILL,

     EmpFTE,

     Conv_Factor,

     USDRate,

     Type,

     [Oct-HCM],

     [Nov-HCM],

     [Dec-HCM],

     [Jan-HCM]

FROM

(ooxml, embedded labels, table is [qProjWorkloads_2 (2)]);

STORE Effort into Effort.qvd;

DROP Table Effort;

/////Then load qvds here

Tab1:

LOAD [Approved BP ID],

     [Billable Product],

     [Billable Project],

     [Project Name],

     Proj_ID,

     [Project Type],

     Res_ID,

     [Functional Manager Name],

     [Cost Centre ID],

     [Cost Centre Descr],

     [Employment Category Description],

     [Billable Resource?],

     [Resource Active?],

     PRIMARY_ROLE,

     PRIMARY_SKILL,

     EmpFTE,

     Conv_Factor,

     USDRate,

     Type,

     Effort,

     Data,

     'Effort' as TableFlag  //Add Flag Here

FROM

(qvd);

Concatenate(Tab1)

LOAD [Approved BP ID],

     [Billable Product],

     [Billable Project],

     [Project Name],

     Proj_ID,

     [Project Type],

     Res_ID,

     [Functional Manager Name],

     [Cost Centre ID],

     [Cost Centre Descr],

     [Employment Category Description],

     [Billable Resource?],

     [Resource Active?],

     PRIMARY_ROLE,

     PRIMARY_SKILL,

     EmpFTE,

     Conv_Factor,

     USDRate,

     Type,

     HOURS,

     Data,

     'Hours' as TableFlag //Add flag here

FROM

(qvd);

Regards

Anand

[Anonymous]

Thanks Anand.. Few queries on the above script

Do i need to create a new QV file while loading the script from Tab1 onwards?

How do i get the path for qvd from the desktop?

[HirisH_V7]

Hi ,

Thats Clear from anand,

Copy paste code and do  your modifications locally ,

I have attached the app check it ,

PFA,

-Hirish

[its_anandrjs · Accepted solution]

The above is the example you can load all the loads in single QV or may be you can use two QV for this. The above load is not with Relative Path.

You can change your location path but only path.

Regards,

Anand

[Anonymous]

Thanks Anand. It has worked.