topic Re: loading multiple cross table in QlikView

loading multiple cross table

Anonymous — Wed, 30 Dec 2015 08:07:25 GMT

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

Re: loading multiple cross table

its_anandrjs — Wed, 30 Dec 2015 08:11:11 GMT

You did not attached any sample data please check.

Regards,

Anand

Re: loading multiple cross table

Anonymous — Wed, 30 Dec 2015 08:16:21 GMT

Files attached. Thse

Re: loading multiple cross table

Anonymous — Wed, 30 Dec 2015 08:17:30 GMT

Files attached

Re: loading multiple cross table

its_anandrjs — Wed, 30 Dec 2015 08:35:52 GMT

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

Re: loading multiple cross table

Anonymous — Wed, 30 Dec 2015 09:38:17 GMT

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?

Re: loading multiple cross table

HirisH_V7 — Wed, 30 Dec 2015 10:03:46 GMT

Hi ,

Thats Clear from anand,

Copy paste code and do your modifications locally ,

I have attached the app check it ,

PFA,

-Hirish

Re: loading multiple cross table

its_anandrjs — Wed, 30 Dec 2015 10:07:50 GMT

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

Re: loading multiple cross table

Anonymous — Wed, 30 Dec 2015 11:12:57 GMT

Thanks Anand. It has worked.