Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

How do I query specific rows of a field to load - Qlik Sense

Hi,

I would like to load 3 fields from a table: [Deal Name], [P&L Impact], [Trans Type] however,

I only want to pull the rows from [Deal Name] that equal any of the below from the field [Trans Type]

[Trans Type]

'Return of Capital - Debt', 'Return of Capital - Equity Private', 'Return of Capital - Equity Public', 'Return of Capital - Fund', 'Return of Capital - Equity Private', 'Return of Capital - Fund Affiliate Portion'

My pseudo example: SELECT [Deal Name] WHERE [TRANS TYPE] = ANY of the following 'Return of Capital - Debt', 'Return of Capital - Equity Private', 'Return of Capital - Equity Public', 'Return of Capital - Fund', 'Return of Capital - Equity Private', 'Return of Capital - Fund Affiliate Portion'


What I've tried so far:


Exit_Details:

LOAD

[Deal Name],

[P&L Impact],

[Trans Type] as "Trans Type_ED"

FROM [lib://Data/xxx]

(ooxml, embedded labels, table is [xxx ])

WHERE MATCH([Trans Type], 'Return of Capital - Debt', 'Return of Capital - Equity Private', 'Return of Capital - Equity Public', 'Return of Capital - Fund', 'Return of Capital - Equity Private', 'Return of Capital - Fund Affiliate Portion' ) > 0;


This limits the [Trans Type] when I put it in a filter pane, however if I put [Deal Name] into a table it still pulls every deal name regardless of [Trans Type].

How do I do this in the QS LOAD script?

TIA.

3 Replies
danansell42
Not applicable

Re: How do I query specific rows of a field to load - Qlik Sense

I think you should need to remove the '>0' from the end of line 8

lakshmikandh
Not applicable

Re: How do I query specific rows of a field to load - Qlik Sense

or try  IF(MATCH([Trans Type], 'Return of Capital - Debt', 'Return of Capital - Equity Private', 'Return of Capital - Equity Public', 'Return of Capital - Fund', 'Return of Capital - Equity Private', 'Return of Capital - Fund Affiliate Portion' ),1,0)=1

eightieschild
Not applicable

Re: How do I query specific rows of a field to load - Qlik Sense

You could try inline loading the match values into a temp table that you use to KEEP LOAD the master table results against, then drop the temp table.

Something like:

Temp:

LOAD * INLINE [

    "Trans Type_ED"

    'Return of Capital - Debt'

    'Return of Capital - Equity Private'

    'Return of Capital - Equity Public'

    'Return of Capital - Fund'

    'Return of Capital - Equity Private'

    'Return of Capital - Fund Affiliate Portion'

];

LEFT KEEP LOAD 

[Deal Name], 

[P&L Impact], 

[Trans Type] as "Trans Type_ED" 

FROM [lib://Data/xxx]  (ooxml, embedded labels, table is [xxx ]);

DROP TABLE Temp;

Chris