topic Re: How to combine 2 Table into 1 ? in QlikView

How to combine 2 Table into 1 ?

paulyeo11 — Thu, 09 Mar 2017 06:50:08 GMT

Hi All

I have 2 Expression for Table 1 & 2 as below , May i know how to combine both table into one ?

I try to do , but i don't know where to start. Hope some one can advise me.

Paul

Table 2 Expression :-

money(

SUM({<year = {'$(=Max(year)-1)'}, month = {"<=$(=IF(GetSelectedCount(month), Max({<year = {'$(=Max(year))'}>}month), Num(Month(Today()))))"}>}$(ColumnDim51)/$(Columndim89)/1000)

, $(vMoneyFormatK))

Table 1 Expression :-

(

match([Pls select 70 Dim],'rEVENUE'),

money(

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},$(ColumnDim71)={$(ColumnDim71)}>}Amount*1)/$(Columndim89)/1000,

$(vMoneyFormatK)),

(

match([Pls select 70 Dim],'sales'),

money(

sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}$(ColumnDim51)/$(Columndim89)/1000),

$(vMoneyFormatK)),

(

match([Pls select 70 Dim],'eXP'),

money(

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},$(ColumnDim71)={$(ColumnDim71)}>}Amount*-1)/$(Columndim89)/1000,

$(vMoneyFormatK)),

(

match([Pls select 70 Dim],'n_PRO'),

money(

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},$(ColumnDim71)={$(ColumnDim71)}>}Amount*1)/$(Columndim89)/1000,

$(vMoneyFormatK)),

))))

(

match([Pls select 66 Dim],'rEVENUE'),

money(

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/1000,

$(vMoneyFormatK)),

(

match([Pls select 66 Dim],'sales'),

money(

sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}$(ColumnDim51)/$(Columndim89)/1000)

, $(vMoneyFormatK))

(

match([Pls select 66 Dim],'eXP'),

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/1000,

(

match([Pls select 66 Dim],'n_PRO'),

SUM({<year = {"$(=Max(year)-1)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/1000,

))))

(

match([Pls select 66 Dim],'rEVENUE'),

SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/1000,

(

match([Pls select 66 Dim],'sales'),

sum({$<year = {$(=max(year)-0)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}$(ColumnDim51)/$(Columndim89)/1000),

(

match([Pls select 66 Dim],'eXP'),

SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/1000,

(

match([Pls select 66 Dim],'n_PRO'),

SUM({<year = {"$(=Max(year)-0)"},month={"<=$(=month)"},$(ColumnDim51)={$(ColumnDim51)}>}Amount*1)/1000,

))))

Re: How to combine 2 Table into 1 ?

paulyeo11 — Thu, 09 Mar 2017 06:52:09 GMT

my QVW

Re: How to combine 2 Table into 1 ?

rahulpawarb — Thu, 09 Mar 2017 07:12:33 GMT

Hello Paul,

Trust that you are doing well!

Assuming that both the expressions are showing desired results, I have combined them into Table 2. Please refer the attached sample application. I still suspect there something which I missed during implementation.

Kindly review and let me know if this works.

Regards!

Rahul

Re: How to combine 2 Table into 1 ?

paulyeo11 — Thu, 09 Mar 2017 07:25:43 GMT

Hi Sir

I am fine , thank you for your sharing.

What i want to make the 2 expression into 1 expression.

So that my Table only have 1 Column. That can display all information .

Now it is 2 column.

My final table will be 3 column , column 1 LY Column 2 CY and Column 3 Vari.

Paul

Re: How to combine 2 Table into 1 ?

adamdavi3s — Thu, 09 Mar 2017 07:38:22 GMT

Hi Paul,

If you're trying to combine two expressions into one then you might need to create a calculated dimension and use pick / match (please see attached document)

Or, you can get really fancy and do something like the KPI report which I have blogged about here:

http://qlikanddirty.com/2017/01/06/custom-kpi-report-using-excel-template-and-variables/

Re: How to combine 2 Table into 1 ?

rahulpawarb — Thu, 09 Mar 2017 07:43:38 GMT

Hello Paul,

As per my understanding, I have combined two expressions into one separated by '|' (pipe). Please refer the attached sample expression.

Hope this will help.

Regards!

Rahul