Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
caccio88
Contributor 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

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);

4 Replies

Re: SumIFlike per rows depending on two different sources

Don't see any attachment. Can you reattach?

Employee
Employee

Re: SumIFlike per rows depending on two different sources

"Here is my excel attached w" nothing attached

caccio88
Contributor II

Re: SumIFlike per rows depending on two different sources

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


thx

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);