Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

SumIFlike per rows depending on two different sources

Hi everyone,

what I'm looking for is to create a field in Qlikview like what I've done in Excel.

I thought it would be simple to replicate, but it's not to me.

Here is my excel attached with my formula in the "L" column of "DB_Fatt" sheet.

Thank u so much.

Filiberto

sunindiaswuehl

1 Solution

Accepted Solutions
Highlighted

Re: SumIFlike per rows depending on two different sources

Try this:

MappingTable:

Mapping

LOAD CUSTOMER_TRX_ID,

    -Sum(IMPORTO_VAL_SOB)

FROM

[Example (4).xlsx]

(ooxml, embedded labels, table is DB_Inc)

Group By CUSTOMER_TRX_ID;

MappingTable2:

Mapping

LOAD CUSTOMER_TRX_ID,

    Sum(INV_DIST_BASE_AMOUNT)

FROM

[Example (4).xlsx]

(ooxml, embedded labels, table is DB_Fatt)

Group By CUSTOMER_TRX_ID;

Table:

LOAD CUSTOMER_TRX_ID,

    ORGANIZATION_ID,

    ORGANIZ,

    TRX_NUMBER,

    LINE_NUM,

    CUST_CODE,

    CUST_NAME,

    INV_GL_DATE,

    INV_DIST_BASE_AMOUNT,

    ACCOUNT_DIST,

    ITEM,

    ImportoIncassi as ImportoIncassi_OLD,

    Alt((ApplyMap('MappingTable', CUSTOMER_TRX_ID, 0) * INV_DIST_BASE_AMOUNT)/ApplyMap('MappingTable2', CUSTOMER_TRX_ID, 0), 0)  as ImportoIncassi_NEW

FROM

[Example (4).xlsx]

(ooxml, embedded labels, table is DB_Fatt);

View solution in original post

4 Replies
Highlighted

Re: SumIFlike per rows depending on two different sources

Don't see any attachment. Can you reattach?

Highlighted
Employee
Employee

Re: SumIFlike per rows depending on two different sources

"Here is my excel attached w" nothing attached

Highlighted
Creator II
Creator II

Re: SumIFlike per rows depending on two different sources

Damn! I don't know why it was not uploaded before ...


thx

Highlighted

Re: SumIFlike per rows depending on two different sources

Try this:

MappingTable:

Mapping

LOAD CUSTOMER_TRX_ID,

    -Sum(IMPORTO_VAL_SOB)

FROM

[Example (4).xlsx]

(ooxml, embedded labels, table is DB_Inc)

Group By CUSTOMER_TRX_ID;

MappingTable2:

Mapping

LOAD CUSTOMER_TRX_ID,

    Sum(INV_DIST_BASE_AMOUNT)

FROM

[Example (4).xlsx]

(ooxml, embedded labels, table is DB_Fatt)

Group By CUSTOMER_TRX_ID;

Table:

LOAD CUSTOMER_TRX_ID,

    ORGANIZATION_ID,

    ORGANIZ,

    TRX_NUMBER,

    LINE_NUM,

    CUST_CODE,

    CUST_NAME,

    INV_GL_DATE,

    INV_DIST_BASE_AMOUNT,

    ACCOUNT_DIST,

    ITEM,

    ImportoIncassi as ImportoIncassi_OLD,

    Alt((ApplyMap('MappingTable', CUSTOMER_TRX_ID, 0) * INV_DIST_BASE_AMOUNT)/ApplyMap('MappingTable2', CUSTOMER_TRX_ID, 0), 0)  as ImportoIncassi_NEW

FROM

[Example (4).xlsx]

(ooxml, embedded labels, table is DB_Fatt);

View solution in original post