Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Calculated Field in If Match Statement

I have an App that is linked to our GL and I am trying to create a field that rolls these GLs up into the line totals people are used to seeing.

For example, say GL account numbers 900000 and 910000 are both included in our Gasoline Sales P&L Line, and 905000 and 915000 are both included in our Diesel Sales P&L Line, I have rolled these up so that they are included in a new field I named P&L Line. See below:

If(Match([AccountNum],'900000','910000'),'01. Gas Gallons',

    If(Match([AccountNum],'905000','915000'),'04. Diesel Gallons','Other')) AS 'P&L Line'


These are all working as expected, but my issue is that I also want to have a third 'P&L Line' that totals these two as 'Total Gallons' and haven't figured out how I can have all three.

TIA!

3 Replies
Highlighted

Re: Calculated Field in If Match Statement

You will have to use Where statement with Concatenation....

Fact:

LOAD AccountNum,

     OtherFields

FROM ....;

LinkTable:

LOAD AccountNum,

     If(Match([AccountNum],'900000','910000'),'01. Gas Gallons',

          If(Match([AccountNum],'905000','915000'),'04. Diesel Gallons','Other')) AS 'P&L Line'

Resident Fact;


Concatenate (LinkTable)

LOAD AccountNum,

     'Total Gallons' as 'P&L Line'

Resident Fact

Where Match([AccountNum],'900000','910000', '905000','915000')

Highlighted
Contributor II
Contributor II

Re: Calculated Field in If Match Statement

Thank you Sunny!

This did work to give me Total Gallons, but now that is all I have, rather than having all three: Total Gallons, Gas Gallons, and Diesel Gallons.

Any suggestions on how to add Total Gallons as a 'P&L Line' in addition to Gas Gallons & Diesel Gallons?

Highlighted

Re: Calculated Field in If Match Statement

Did you have this in your script?

Fact:

LOAD AccountNum,

    OtherFields

FROM ....;

LinkTable:

LOAD AccountNum,

    If(Match([AccountNum],'900000','910000'),'01. Gas Gallons',

          If(Match([AccountNum],'905000','915000'),'04. Diesel Gallons','Other')) AS 'P&L Line'

Resident Fact;


Concatenate (LinkTable)

LOAD AccountNum,

    'Total Gallons' as 'P&L Line'

Resident Fact

Where Match([AccountNum],'900000','910000', '905000','915000')