Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mikaschm61
New Contributor

Remove defined rows

Hello Qlik friends,


I have a little questions for you.

Maybe you know a way how the problem could be solved. I would like to remove rows in a table.

YearMonthNoType
201801FC
201801BUDGET
201801ACTUAL
201802FC
201802BUDGET
201802ACTUAL
201803FC
201803BUDGET
201804FC
201804ACTUAL

I would like to remove the rows which contains FC, if the same Year and MonthNo contains ACTUAL.

Hopefully, I could explain my little problem.

Thank you very much for your support.

Best regards,

Tags (1)
4 Replies

Re: Remove defined rows

Replace the Type dimension with a measure with this expression: only({<Type -= {'FC'} >}Type)


talk is cheap, supply exceeds demand
mikaschm61
New Contributor

Re: Remove defined rows

Thanks for your answer, I would like to do it in the Script.

I tried it with a Where Clause, but its not the correct way.

Table:

Load * Inline [

Year, MonthNo, Type

2018, 01, FC

2018, 01, BUDGET

2018, 01, ACTUAL

2018, 02, FC

2018, 02, BUDGET

2018, 02, ACTUAL

2018, 03, FC

2018, 03, BUDGET

2018, 04, FC

2018, 04, ACTUAL

];

Re: Remove defined rows

1. Where Type <> 'FC'

2. Where Not Match(Type, 'FC')


talk is cheap, supply exceeds demand
shivanandk
Contributor II

Re: Remove defined rows

Try this

Table:

Load Year*100+MonthNo as YearMonth,

*

;

Load * Inline [

Year, MonthNo, Type

2018, 01, FC

2018, 01, BUDGET

2018, 01, ACTUAL

2018, 02, FC

2018, 02, BUDGET

2018, 02, ACTUAL

2018, 03, FC

2018, 03, BUDGET

2018, 04, FC

2018, 04, ACTUAL

];

TableActual:

Load YearMonth as YearMonth_ACTUAL Resident Table

where [Type] = 'ACTUAL' ;

Data:

NoConcatenate

Load * Resident Table where not Exists(YearMonth_ACTUAL,YearMonth) and [Type] = 'FC' ;

Concatenate(Data)

Load * Resident Table where [Type] <> 'FC' ;

Drop Table Table ;

DROP TablE TableActual ;