Field grouping

[dhanavindhan]

Dear Experts,

I want to write expression which needs to group my field value.

My issue:

I have a column name called "Invstatus" under this we have 2 options Invoiced & Not Invoiced. Users enter like dis for invoiced

Eg:Invoiced

Invoiced, Invoiced, Invoiced

Not Invoiced

While I apply condition to show only invoiced amount it takes only field value Invoiced remaing invoiced, invoiced are left out. I need to group them all into single field like dis

Invoiced

Not Invoiced

How can I achieve this by using qlik expression?

[swuehl · Accepted solution]

Maybe like

LOAD

     Invstatus,

     If(Left(Invstatus,3) = 'Not', 'Not Invoiced','Invoiced') as NewInvstatus,

     ...

[captain89]

Hi,

it seems like a description field. Can the user put any value in this field?

There are several ways to group a field but you willl give us a rule to determinate when a row is invoiced.

ERP systems usually have a flag-field with this information.... Y/N. Check with your provider...

for example index function:

LOAD if(index(Upper(Invstatus), 'NOT')>0, 'Not Invoiced', 'Invoiced') as Invoiced?, * INLINE [

    Invstatus

    E.g.: Not Invoiced

    Invoiced

    E.g. Invoiced

    Inv.

    Not. Inv.

    NOT INVOICED

];

Invstatus	Invoiced?
E.g. Invoiced	Invoiced
E.g.: Not Invoiced	Not Invoiced
Inv.	Invoiced
Invoiced	Invoiced
NOT INVOICED	Not Invoiced
Not. Inv.	Not Invoiced