topic Re: Seaching the column for a value in QlikView

Seaching the column for a value

abhi1693r — Wed, 25 Nov 2020 16:16:04 GMT

I have 5 columns A-E, with values 'X' or blank. (Snapshot attached)

Now I want to check if even one 'X' present in one column, add 10 for that column in a new expression.

I am using if(A='X',10,0) + if(B='X',10,0) ........

Somehow it is only working for one of the columns. Does this not search the entire column?

Re: Seaching the column for a value

Anil_Babu_Samineni — Fri, 19 Jul 2019 13:50:38 GMT

How about this?

If(Index(Col1&'|'&Col2&'|'&Col3&'|'&Col4, 'X'), 10, 'No X Present') as New_Column

Re: Seaching the column for a value

Anonymous — Fri, 19 Jul 2019 16:52:52 GMT

If this is on script, instead searching for 'X' try to not find this. Do a resident load

If(Index(Column1, 'X'), 1, 0) as _fgColumn1,
If(Index(Column2, 'X'), 1, 0) as _fgColumn2,
If(Index(Column3, 'X'), 1, 0) as _fgColumn3,
If(Index(Column4, 'X'), 1, 0) as _fgColumn4,
If(Index(Column5, 'X'), 1, 0) as _fgColumn5

Then make a total sum of each _fgColumnY, save on a variable.

Then if the varaible is 0, keep the value of column (ColumnY as newColumnY); if not, add 10 (Column+10 as newColumnY)

Re: Seaching the column for a value

abhi1693r — Mon, 22 Jul 2019 14:43:35 GMT

I guess this will only work if X is present as a character value in Excel, right?