Help

[panipat1990]

Hi,

  I have one requirement..Suppose I have 2 excel file Same Column Name......When I pull in qlikview It should be One File....Means 3 File See like ..When I show in table Box....Then sum Of All Values Apr May Jun in one Table....Please Help Me...

Header 1	Header 2	Header 3	Header 4
Particulers	Apr 2015	May 2015	Jun 2015
Net revenue	12	13	14
Net Cost	11	10	13
Net material	10	14	13

Header 1	Header 2	Header 3	Header 4
Particulers	Apr 2015	May 2015	Jun 2015
Net revenue	13	16	19
Net Cost	11	17	13
Net material	12	11	15

Header 1	Header 2	Header 3	Header 4
Particulers	Apr 2015	May 2015	Jun 2015
Net revenue	25	29	33
Net Cost	22	27	26
Net material	22	25	28

@ !

[sunny_talwar · Accepted solution]

Try this:

Table1:

LOAD * INLINE [

    Particulars, Apr 2015, May 2015, Jun 2015

    Net revenue, 12, 13, 14

    Net Cost, 11, 10, 13

    Net material, 10, 14, 13

];

Concatenate (Table1)

LOAD * INLINE [

    Particulars, Apr 2015, May 2015, Jun 2015

    Net revenue, 13, 16, 19

    Net Cost, 11, 17, 13

    Net material, 12, 11, 15

];

Concatenate (Table1)

LOAD * INLINE [

    Particulars, Apr 2015, May 2015, Jun 2015

    Net revenue, 25, 29, 33

    Net Cost, 22, 27, 26

    Net material, 22, 25, 28

];

FinalTable:

NoConcatenate

LOAD Particulars,

  Sum([Apr 2015]) as [Apr 2015],

  Sum([May 2015]) as [May 2015],

  Sum([Jun 2015]) as [Jun 2015]

Resident Table1

Group By Particulars;

DROP Table Table1;

[MK_QSL]

Data:
CrossTable(MonthYear, Value)
Load * Inline
[
  Particulers, Apr 2015, May 2015, Jun 2015
  Net revenue, 12, 13, 14
  Net Cost, 11, 10, 13
  Net material, 10, 14, 13
];


CrossTable(MonthYear, Value)
Load * Inline
[
  Particulers, Apr 2015, May 2015, Jun 2015
  Net revenue, 13, 16, 19
  Net Cost, 11, 17, 13
  Net material, 12, 11, 15
];


CrossTable(MonthYear, Value)
Load * Inline
[
  Particulers, Apr 2015, May 2015, Jun 2015
  Net revenue, 25, 29, 33
  Net Cost, 22, 27, 26
  Net material, 22, 25, 28
];


NoConcatenate
Final:
Load Particulers, MonthYear, SUM(Value) as Value Resident Data
Group By Particulers, MonthYear;


Drop Table Data;

[panipat1990]

Hi

  I want to Show my list Box Regarding Apr 2015, May 2015, Jun 2015 

                                       25       29       33

                                                                 22          27            26

                                                                 22          25           28

When I select Net Revenue & Apr 2015 Then It show Be 25 Value 

When I Select Net Cost & Apr 2015 Then It Show be      22  Value

When I Select Net Material & Apr 2015  then It Show be      22  Value

[sunny_talwar]

Oh the third table you put was the expected output?

[sunny_talwar]

Try this:

Table1:

LOAD * INLINE [

    Particulars, Apr 2015, May 2015, Jun 2015

    Net revenue, 12, 13, 14

    Net Cost, 11, 10, 13

    Net material, 10, 14, 13

];

Concatenate (Table1)

LOAD * INLINE [

    Particulars, Apr 2015, May 2015, Jun 2015

    Net revenue, 13, 16, 19

    Net Cost, 11, 17, 13

    Net material, 12, 11, 15

];

FinalTable:

NoConcatenate

LOAD Particulars,

  Sum([Apr 2015]) as [Apr 2015],

  Sum([May 2015]) as [May 2015],

  Sum([Jun 2015]) as [Jun 2015]

Resident Table1

Group By Particulars;

DROP Table Table1;

[suniljain]

I will suggest you to implement KPI calculation at backend corresponding to period instead of hardcoding month and year .

If you are doing KPI calculation at back end , it is easy to achieve your report format. below format like

Particulers, Apr 2015, May 2015, Jun 2015  in inline table will not help you because for every additional month, it require script change.