flags | dim for field value combinations

[gino2780]

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?

[agigliotti]

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]

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.

[sunny_talwar]

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]

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.

[sunny_talwar · Accepted solution]

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]

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

Great Job, Mr. MVP.