9 Replies Latest reply: Feb 3, 2014 5:18 AM by Gysbert Wassenaar RSS

    substitute value in table cell

      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!

        • Re: substitute value in table cell
          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

            • Re: substitute value in table cell

              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.

                • Re: substitute value in table cell
                  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.

                    • Re: substitute value in table cell

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

                      Still, can this be achieved somehow in this stage?

                        • Re: substitute value in table cell
                          Gysbert Wassenaar

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

                            • Re: substitute value in table cell

                              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

                                • Re: substitute value in table cell
                                  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.

                                    • Re: substitute value in table cell

                                      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.