Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gino2780
Contributor

flags | dim for field value combinations

Hello dear community,

i've got a problem with generating script-based new fields (flags or dimensions) for picking different field value combinations.

Let me give you some example data:

As you can see in the table above, there are shown several cases with different invoice type combinations between 1st, 2nd and 3rd invoice date. How can i generate flags in the script for different combinations, let's say: show me a flag for ID's with two different invoices (Pipeline ID: 542), show me a flag for three different invoices (Pipeline ID: 337) and so on and so forth.

Any suggestions?

1 Solution

Accepted Solutions

Re: flags | dim for field value combinations

May be do this in a resident load

Table:

LOAD [Pipeline ID],

     [Datum Faktura],

     [1. Faktura],

     [2. Faktura],

     [3. Faktura]

FROM ...;

Left Join (Table)

LOAD [Pipeline ID],

     If(MaxString([1. Faktura]) = '1. Zwischenfaktura' and MaxString([2. Faktura]) = '2. Endfaktura', 1, 0) as Flag1

     If(MaxString([1. Faktura]) = '1. Zwischenfaktura' and MaxString([3. Faktura]) = '3. Korrektur', 1, 0) as Flag2

Resident Table

Group By [Pipeline ID];

6 Replies
agigliotti
Honored Contributor II

Re: flags for field value combinations

I think you should use a crosstable to achieve your request.

take a look at https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/Scripting/ScriptPrefixes/crosstab...

I hope it helps.

gino2780
Contributor

Re: flags for field value combinations

Unfortunately that's not what i'm looking for. I'd like to further classify the invoice types (based on their combinations) either by flags or new dimension in order to use listboxes, e.g.:

combination of invoice type 1 & 2 = invoiced;

combination of invoice type 1 & 2 & 3 = delayed invoice etc.

All aggregated on the ID-level ofc.

Re: flags for field value combinations

Based on the sample data shared above, what is the output you are expecting to see? And is that something you need on the front end of the app or backend scripting?

gino2780
Contributor

Re: flags | dim for field value combinations

Based on the given data i'm looking forward to generate flags or dimensions like:

If([1. Faktura] = '1. Zwischenfaktura' and [2. Faktura]='2. Endfaktura',1,0) as Flag1 /*(for fully invoiced e.g.)*/

If([1. Faktura] = '1. Zwischenfaktura' and [3. Faktura]='3. Korrektur',1,0) as Flag2 /*(not yet invoiced e.g.)*/

These commands won't seem to work, because the data is rowwise connected on ID ([Pipeline ID]).

I need a command that can look up for the three different invoice types [1. Faktura], [2. Endfaktura], [3. Faktura] and combinations per ID [Pipeline ID].

In the frontend i'd like then to use these flags or dimensions as for listboxes in order to differentiate the different combination pattern of invoices.

Re: flags | dim for field value combinations

May be do this in a resident load

Table:

LOAD [Pipeline ID],

     [Datum Faktura],

     [1. Faktura],

     [2. Faktura],

     [3. Faktura]

FROM ...;

Left Join (Table)

LOAD [Pipeline ID],

     If(MaxString([1. Faktura]) = '1. Zwischenfaktura' and MaxString([2. Faktura]) = '2. Endfaktura', 1, 0) as Flag1

     If(MaxString([1. Faktura]) = '1. Zwischenfaktura' and MaxString([3. Faktura]) = '3. Korrektur', 1, 0) as Flag2

Resident Table

Group By [Pipeline ID];

gino2780
Contributor

Re: flags | dim for field value combinations

Sunny, that's exactly what i've been looking for. Thanks!

Great Job, Mr. MVP.

Community Browser