Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
gino2780
Creator
Creator

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
sunny_talwar

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

View solution in original post

6 Replies
agigliotti
Partner - Master III
Partner - Master III

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
Creator
Creator
Author

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
Creator
Creator
Author

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

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
Creator
Creator
Author

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

Great Job, Mr. MVP.