substitute value in table cell

[#1]

Hello community!

Here Im again with  rather simple question at the first sight. Maybe at all...

I have a table like this:

     COL1     COL2     COL3     ...     FIELD     VALUE     QUALIFY

            0           0            0              qwert        zxcvv          COL2

            .

            .

            .

What I need is to replace a value in one of COL-like columns with, say, '1' and it should be that particular column which is a value of QUALIFY column in this row.

Consider a table of like 20 million rows. So I need a very fast way to instruct Qlik to do this operation.

Thank for all your advices.

Cheers!

[Gysbert_Wassenaar]

The script is the place to do this:

LOAD

    if(QUALIFY='COL1', 1, COL1) as COL1,

    if(QUALIFY='COL2', 1, COL2) as COL2,

    if(QUALIFY='COL3', 1, COL3) as COL3,

    FIELD,

    VALUE,

    QUALIFY

FROM ... etc

[#3]

actually, COLlike fields are added to original table dynamically in loop. These fields are transformed from rows to fields and then joined to the original table on some DUMMY field which is dropped then. So COL fields are dynamic.

[Gysbert_Wassenaar]

These fields are transformed from rows to fields and then joined to the original table

And now you have one example of why this often isn't a good idea. Perhaps the actual problem you're trying to solve with this approach can be solved in a better way.

[#5]

well, Im aware of this is not an ideal situation.

Still, can this be achieved somehow in this stage?

[Gysbert_Wassenaar]

No idea. You haven't explained what problem you're trying to solve.

[#7]

well you see what's my problem...
But originally situation is like this:
Two tables:
data:                              quality:

LOAD * INLINE               LOAD * INLINE

[                                    [

     FIELED,                        Q_ID,

     VALUE,                          QUALIFY

     QUALIFY,

     Q_ID                        ];

];

QUALIFY column in second table contains rows with names of columns that I have transformed to COLlike columns and joined them into original data table on some dummy column which was dropped in turn.

I have to have all quality.QUALIFY rows as columns in first table.

In data table I have to have 1 or 0 in each row of transformed COLlike column whose name matches value from QUALIFY column in that row(than its 1 otherwise 0).

I don't want to use nested loops because of performance!

As you can see, I have Q_ID value available in first table, which is fk to second table but... I don't see a use of it.

So I got to the point which I described in first post and I hope Qlik will assist me in some very wise way to get it to its end

[Gysbert_Wassenaar]

QUALIFY column in second table contains rows with names of columns that I have transformed to COLlike columns and joined them into original data table on some dummy column which was dropped in turn.

You still haven't explained the problem. You're fixated on a chosen solution instead and have elevated that as your problem. Why are you doing that transformation and joining? What is the problem that that is supposed to solve?

It's like you're standing on the side of a river and want to get across and you say "My problem is there is no bridge. Please help me build a bridge". If you say instead "My problem is I need a way to get to the other side of the river" somebody might point out to you there's a tunnel 50 meters to your right you can use.

[#9]

well my initial post is where I got myself and was thinking that qlik can somehow help me to get to its end.

secondly, in previous post Im describing a situation which I am facing due to business decisions made and from this point my work begins. So this is it that I can tell you, if I could tell more, I would, Im sure it would simplify things..

So it's like Im standing on a side of river and I need to get to the other side while I have this tools to made that way.

[Gysbert_Wassenaar]

Well, good luck with your bridge then. If you don't want to explain what the end result that users get to work with should be and what your source data looks like, I cannot help you.