Quarter for synthetic keys.

[Kohli]

Quat2014:

LOAD

    EmployeeID as EmpID,

    SalesPerson,

    Q1,

    Q2,

    Q3,

    Q4

FROM [lib://Soure Data/Quota.xls]

(biff, embedded labels, header is 1 lines, table is [Quota 2014$]);

Quat2015:

LOAD

    EmployeeID as EmpID,

    Q1,

    Q2,

    Q3,

    Q4

FROM [lib://Soure Data/Quota.xls]

(biff, embedded labels, header is 1 lines, table is [Quota 2015$]);

How can i find the sales in 2014 Quarte and 2015 Quarter.

[YoussefBelloum]

Hi,

here it depends on what you need in terms of modelisation.

you can join your tables using the joining field EmpID and renaming the Quarter fields using the Year as a description, like this Q1_2015, Q2_2015 and Q1_2014, Q2_2014

example

Quat2014:

LOAD

    EmployeeID as EmpID,

    SalesPerson,

    Q1 as ,Q1_2014

    Q2 as Q2_2014,

    Q3 as Q3_2014,

    Q4 as Q4_2014

FROM [lib://Soure Data/Quota.xls]

(biff, embedded labels, header is 1 lines, table is [Quota 2014$]);

left join //or any other type of join

Quat2015:

LOAD

    EmployeeID as EmpID,

    Q1 as Q1_2015,

    Q2 as Q2_2015,

    Q3 as Q3_2015,

    Q4 as Q4_2015

FROM [lib://Soure Data/Quota.xls]

(biff, embedded labels, header is 1 lines, table is [Quota 2015$]);

or you can concatenate if you don't want or can't rename your fields

example:

Quat2014:

LOAD

    EmployeeID as EmpID,

    SalesPerson,

    Q1,

    Q2,

    Q3,

    Q4

FROM [lib://Soure Data/Quota.xls]

(biff, embedded labels, header is 1 lines, table is [Quota 2014$]);

concatenate

Quat2015:

LOAD

    EmployeeID as EmpID,

    Q1,

    Q2,

    Q3,

    Q4

FROM [lib://Soure Data/Quota.xls]

(biff, embedded labels, header is 1 lines, table is [Quota 2015$]);

[petter]

Quat2014 does not have a SalesPerson field. That is why you get a synthetic key. If you can either exclude SalesPerson if that field is not relevant or include SalesPerson in the Quat2015 if it is available in the sheet in the Excel workbook.

You should also do a CrossTable load to unpivot the quarters into a Sales field - that will make analysis and building the dashboard much easier.

I would suggest something like this:

SALES:

CrossTable( Quarter, Sales, 3) LOAD

    EmployeeID as EmpID,

    SalesPerson,

    2014 AS Year,

    Q1,

    Q2,

    Q3,

    Q4

FROM [lib://Soure Data/Quota.xls]

(biff, embedded labels, header is 1 lines, table is [Quota 2014$]);

CrossTable( Quarter, Sales, 3 ) LOAD

    EmployeeID as EmpID,

    SalesPerson,

     2015 AS Year,

    Q1,

    Q2,

    Q3,

    Q4

FROM [lib://Soure Data/Quota.xls]

(biff, embedded labels, header is 1 lines, table is [Quota 2015$]);

Since the two tables have exactly the same field Qlik will do an auto concatenation into a single table named SALES.

In your app you can now use expressions like these:

Sum( {<Year={2015}>} Sales ) - Sum( {<Year={2014}>} Sales )

or

Sum( {<Year={2015},Quarter={'Q1'}>} Sales ) - Sum( {<Year={2014},Quarter={'Q1'}>} Sales )

[arvind_patil]

Hi

You can add Dummy filed Year  as mentioned above :

SALES:

CrossTable( Quarter, Sales, 3) LOAD

    EmployeeID as EmpID,

    SalesPerson,

    2014 AS Year,

    Q1,

    Q2,

    Q3,

    Q4

FROM [lib://Soure Data/Quota.xls]

(biff, embedded labels, header is 1 lines, table is [Quota 2014$]);

CrossTable( Quarter, Sales, 3 ) LOAD

    EmployeeID as EmpID,

    SalesPerson,

     2015 AS Year,

    Q1,

    Q2,

    Q3,

    Q4

FROM [lib://Soure Data/Quota.xls]

(biff, embedded labels, header is 1 lines, table is [Quota 2015$]);

Thanks,

Arvind Patil