Joining/Loading Conditions of 2 tables

[dmohanty]

Hi All,

I have a scenario to load two different tables from Excel File into QlikView. The two tables have below metioned fields. Could some one please help me on How to Load or Join or Transform the tables into QlikView?

Do I need to Join or make a Link Table or any other suggestions?

Table 1 (From Excel 1):

Rev Class

Month

QUARTER

Com Code

Costctr

Final Vertical

SBU

CC Desc

Cust num

Cust Name

Group Customer Name

Geo

Final New SubVertical

Proj Type

Project Code

W B S Code

Acct Id

Acct Desc

Org Unit

Org Unit Description

Staffing %

Emp Code

Emp Name

Grade

Practice

Previous Exp(Yrs)

Previous Exp(Mon)

Wipro Exp(Yrs)

Wipro Exp(Mon)

SOW No

Customer Engagement Type

Project Classification

CRM Reference No.

Onsite BMMs

Offshore BMMs

Total BMM

Onsite rate

Offshore rate

Onsite revenue

Offshore revenue

Reimbursement

Pass through

Total revenue

Old Vertical

Bulge Mix

Total Years

Grp Cust Enagaement Revised

Table 2 (From Excel 2):

			Rev ( In Mn)		Rev ( In Mn)	Rev ( In Mn)	Order booking ( In Mn)			Order booking ( In Mn)
SBU	Corrected Name/Group Customer	Sum of FY14 (P)	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4

Regards!

[dmohanty]

Hi All,

Any help on above request please?

Regards!

[CELAMBARASAN]

It would be good to see in excel file with these headers? Because that was the source. Can you upload sample excel file

[dmohanty]

Hi Adhimulam,

Thanks for the request.

As requested, attaching the file (refer Sheet1 and Sheet2) with 15 rows of some sample data.

COuld you please help?

[CELAMBARASAN]

Will this be a fixed header?

or there might be any missing/additional column'?

Which field is the link between this 2 sheets?

[dmohanty]

Hi,

This is the uptodate no. of columns to be used. I dont think there will be any missing/additional columns?

Do you see any challenges here? Please let me know.

[CELAMBARASAN]

Could you please tell me Where you got stuck in this?

[dmohanty]

Hi,

From a development perspective I am new to this. Just wanted to know, directly can I load these tables, or need to Join these (based on some keys) or Link Tables?

In Short, what would be the better approach to proceed? Because, in 1st sheet, I have a filed called Quarter, in 2nd sheet, I have different quarters as different fields. Like this I have concerns.

[CELAMBARASAN]

Use the below script to transform the second sheet.

First sheet you can load as it is. You have to decide which field to be link between two tables and make that field as same name between two tables.

Revenue:

CrossTable(Quarter,Rev,3)

LOAD SBU,

     [Corrected Name/Group Customer],

     [Sum of FY14 (P)],

     Q1,

     Q2,

     Q3,

     Q4

FROM

Sample2Tables.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet2);

OrderBooking:

CrossTable(Quarter,OrderBooking,2)

LOAD SBU,

     [Corrected Name/Group Customer],

     Q11 AS Q1,

     Q21 AS Q2,

     Q31 AS Q3,

     Q41 AS Q4

FROM

Sample2Tables.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet2);

Left Join(Revenue)

LOAD

  *

Resident OrderBooking;

DROP Table OrderBooking;

[dmohanty]

Thank you very much.

Seems you have used Cross Table approach to load this. Ok. I will have an hands on on this format.

and I will post my progress in this thread.