Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

panipat1990
Contributor II

Help

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 1Header 2Header 3Header 4
ParticulersApr 2015May 2015Jun 2015
Net revenue121314
Net Cost111013
Net material101413

Header 1Header 2Header 3Header 4
ParticulersApr 2015May 2015Jun 2015
Net revenue131619
Net Cost111713
Net material121115
Header 1Header 2Header 3Header 4
ParticulersApr 2015May 2015Jun 2015
Net revenue252933
Net Cost222726
Net material222528

@ !

1 Solution

Accepted Solutions
MVP
MVP

Re: Help

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;


Capture.PNG

6 Replies
MVP
MVP

Re: Help

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;


Capture.PNG

Re: Help

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
Contributor II

Re: Help

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

Highlighted
MVP
MVP

Re: Help

Oh the third table you put was the expected output?

MVP
MVP

Re: Help

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
Honored Contributor

Re: Help

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.