2 Replies Latest reply: Jun 9, 2017 10:06 AM by Michele De Nardi RSS

    RowNo() Script wrong result

    Dave Loop

      Hi,

       

      have a look on my script:

       

      Abschriften:

      Load * inline

      [

      Markt,Artikel,Abschrift_1,Abschrift_2

      1,Buch, -1.10, -5.25

      1,Brot, -2.50, -7.75

      1,Wasser, -0.95, -0.70

      1,Apfel, -1.10, -0.80

      1,Salat, -4.50, -2.25

      2,Tee, -7.00, -4.50

      2,Kaffee, -1.25, -3.25

      2,Birne, -3.50, -1.25

      2,Schokolade, -2.50, 0.45

      2,Deo, -1.75, 0.85

      ];

       

      Tmp_Markt:

      NoConcatenate

      LOAD Distinct

          Markt as Tmp_Markt_Field

      Resident Abschriften;

       

      Final:

      NoConcatenate

      LOAD * Inline

      [

      ID,

      0

      ];

       

      Set a=0;

       

      Do while a<=1

          LET vMarkt = peek('Tmp_Markt_Field', $(a), 'Tmp_Markt');

         

          Concatenate(Final)

          Load

              Markt as TestMarkt

              , Artikel as TestArtikel

              , RowNo()-1 as Rank

          Resident Abschriften where Markt = $(vMarkt) order by Abschrift_1;

       

          Let a=a+1;

      Loop

       

       

      drop field ID;

      drop table Tmp_Markt;

       

      I want to have a rank based on the KPI Abschrift1 per market. But qlik sense gives me back a rank on total. But I don't know why? First I rank the artikel for makrte 1 and make a cocanenation on Final and then he takes the market 2 and makes a cocncatenoin on Final. But the result is in total for Rank 1,2,3,4,5,6,7,8,9,10 but I need thik like 1,2,3... for market1 and 1,2,3... for market two.

       

      Where is the mistake?

        • Re: RowNo() Script wrong result
          Stefan Wühl

          That's by design, Rowno() will consider all rows of the concatenated tables.

           

          Try

           

           

           

          Abschriften:

          Load * inline

          [

          Markt,Artikel,Abschrift_1,Abschrift_2

          1,Buch, -1.10, -5.25

          1,Brot, -2.50, -7.75

          1,Wasser, -0.95, -0.70

          1,Apfel, -1.10, -0.80

          1,Salat, -4.50, -2.25

          2,Tee, -7.00, -4.50

          2,Kaffee, -1.25, -3.25

          2,Birne, -3.50, -1.25

          2,Schokolade, -2.50, 0.45

          2,Deo, -1.75, 0.85

          ];

           

           

          NoConcatenate

          LOAD *, AutoNumber(RecNo(), Markt) as Rank

          Resident Abschriften

          Order by Markt, Abschrift_1;

           

           

           

           

          DROP TABLE Abschriften;

          • Re: RowNo() Script wrong result
            Michele De Nardi

            Try this solution:

             

            Abschriften:

            Load * inline

            [

            Markt,Artikel,Abschrift_1,Abschrift_2

            1,Buch, -1.10, -5.25

            1,Brot, -2.50, -7.75

            1,Wasser, -0.95, -0.70

            1,Apfel, -1.10, -0.80

            1,Salat, -4.50, -2.25

            2,Tee, -7.00, -4.50

            2,Kaffee, -1.25, -3.25

            2,Birne, -3.50, -1.25

            2,Schokolade, -2.50, 0.45

            2,Deo, -1.75, 0.85

            ];

             

            Tmp_Markt:

            NoConcatenate

            LOAD Distinct

                Markt as Tmp_Markt_Field

            Resident Abschriften;

             

            let a=0;

             

            For i=0 to NoOfRows('Tmp_Markt')-1;

              let vMarkt=Peek('Tmp_Markt_Field',i,'Tmp_Markt');

             

              Final:

              Load

              Markt as TestMarkt,

                Artikel as TestArtikel,

                rowno()-$(a) as Rank

                Resident Abschriften where Markt=$(vMarkt);

              

                let a=NoOfRows('Final');

              

            next

             

            drop Table Tmp_Markt;

             

            2017-06-09 16_05_57-QlikView x64 Personal Edition - [C__Users_Michele.Denardi_Desktop_test.qvw].png