34 Replies Latest reply: Feb 25, 2012 8:14 AM by swuehl RSS

How to have the Max RowNo from an expression available for all rows?

doschafe

The below provides the RowNo for the latest retrieved value in the system.

 

// expression 1:

= if(max(retrieved) = max(TOTAL retrieved), RowNo(),0)

 

My impression is that it would be simple to have that RowNo available on all rows by wrapping the above expression in MAX, it does not work. 

 

// expression 2:

= MAX(   if(max(retrieved) = max(TOTAL retrieved), RowNo(),0)   )

 

Does anyone see what I am doing wrong in this very simple expression?

 

Simply need to be able to have that max RowNo from the first expression available on every for another expression.

 

Thanks for any assistance....

 

D

  • How to have the Max RowNo from an expression available for all rows?
    swuehl

    You can't just embed one aggregation funcion into another (max() into max() ) like this.

    You would need to use advanced aggregation, using a dimension list for the aggr() function.

     

    Honestly, I haven't fully understood your issue, could you post a sample?

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    How about this, adding in use of Set Analysis which basically does the calc regardless of the selection filter but honors the dimensions: 

     

    =Max({1} total aggr(NODISTINCT if(max({1}TOTAL retrieved) = max({1} retrieved), RowNo(),0) ,YrWk))

    • How to have the Max RowNo from an expression available for all rows?
      swuehl

      Maybe, I also tried with clearing the selection one can make in the selection multi box.

       

      If you use an expression with set identifier 1 as you did in your posted expression, I believe you will always get the '46' max rownumber regardless of selections.

      But if  you select Week(s) from your multibox, your future row v maybe e.g. only 30, so the expression doesn't seem right to me.

       

      Honestly, I haven't fully understood what you are doing and your table looks too complex to get a quick insight.

      Maybe you could work with a set identifier {1} or by clearing the four field you can select in your multibox, like

      {<component=, subcomponent=,cf_rpn=,retrieved=>}

      - maybe not.

       

      --

      Stefan

  • How to have the Max RowNo from an expression available for all rows?
    doschafe

    Stefan,

     

    I must be missing something, doing a very similar expression as before  but getting issue, you see what is incorrect?

     

    This expression gives me the rowno for YrWk=2012-5 as 44 which is correct.

     

    =if(YrWk='2012-5', RowNo(),0)                                                    // gives me 44 for that row

     

    This expression that makes that number available on all rows gives an incorrect answer of 36, you see what I am diong wrong:

     

    =Max(TOTAL aggr(NODISTINCT if(YrWk='2012-5', RowNo(),0)   ,YrWk)  )              

    • How to have the Max RowNo from an expression available for all rows?
      swuehl

      Getting closer.

       

      I believe aggr() function will sort the given dimensions by load order, and you can't change that behaviour.

      Your load order of field YrWk seems not to be numerical asc or text asc. So your second expression will have a different row number representing value '2012-5' then your table (which is sorted properly). If you sort your table dimension by load order, you will see the expression will match, but the overall outcome is not what you want.

       

      Try using an appropriate load order?

       

      Regards,

      Stefan

  • How to have the Max RowNo from an expression available for all rows?
    doschafe

    Stefan,

     

    Thanks... I added an ID column and sorted the table in excel  by the ID column, but no help.  Must be I have to sort the excel file on the way into Qlikview, is that what you are saying?  Is that as easy as adding an ASC to the load by chance?

     

    Thanks,

     

    Don

    • How to have the Max RowNo from an expression available for all rows?
      swuehl

      Not sure what you want to do with excel, is Excel your data source? I currently don't have access to the last version of your qvw-file, so I can't look at the load statement.

       

      The load order of field YrWk is determined by the order your distinct values are read into your data model, this could be done using multiple loads, concatenation etc.

       

      You could try creating an autogenerated table with field YrWk created for the range you need upfront, sorted ascending, then load your other tables in, then I think you can drop your initially created table again.

      Or alternatively take care that the field values are read in sorted from your table source(s).

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Stefan,

     

    The data source is excel currently for all the data but may change.

     

    The data source for the dates dimension is excel, added on so could do the projection and have the future dates that are not in the current data.

     

    Attached is the file that has the dates dimension I mentioned, look at the YYYYWW tab in excel.

     

    The Qlikview load of that excel table is:

     

    LOAD

    IdVal,

    YrWk

    FROM

    C:\Users\doschafe\Documents\Qlikview\DATA\Error_Burn_Down_spec3_Open.xlsx

    (ooxml, embedded labels, table is YYYYWW);

     

    Note, I am not sure how to do the autogenerated item you mentioned, so created this table in excel to move forward.

     

    Can you see what I am doing?

     

    Thanks,

     

    Don

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Stefan,

     

    I have tried a few combinations of trying to sort in Qlikview, does not see to do the trick. 

     

    If I change my import for this table to following, all still ok.  This table joins the existing synthetic join table that was given to me:

     

    BurnDownDates:

    LOAD

    IdVal,

    YrWk

    FROM

    C:\Users\doschafe\Documents\Qlikview\DATA\Error_Burn_Down_spec3_Open.xlsx

    (ooxml, embedded labels, table is YYYYWW);

     

     

    If I then try versions to sort this data in qlikview on load, DOES NOT  work.  What I mean by not working is there is no table joined to the existing synthetic table any longer with the future dates and the future dates disappear.  Ideas??? 

     

    BurnDownDates:

    LOAD

    IdVal,

    YrWk

    FROM

    C:\Users\doschafe\Documents\Qlikview\DATA\Error_Burn_Down_spec3_Open.xlsx

    (ooxml, embedded labels, table is YYYYWW);

     

     

    YYYYWW:

    LOAD

    IdVal,

    YrWk

    resident BurnDownDates

    order by IdVal;

     

     

    Drop Table BurnDownDates;

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Ok, have not played with resident files and Excel and sorting.  Played with loading excel files, inline data, using resident tables and sorting a bit on extremely small file to sort out the quirks.  Think i have the kinks worked out in a test file, now will try on the large file.  Here is what I did:

     

    // Excel Load, Sort Resident, Drop Original

     

     

    BurnDownDates:

    LOAD

              ColA,

              ColB

    FROM

    U:\Qlikview_CURR\Error_Burn_Down_spec3_Open.xlsx

    (ooxml, embedded labels, table is YYYYWW);

     

     

     

     

    SortedBDD:

    LOAD

    ColA As IdVal,

    ColB As YrWk

    resident BurnDownDates

    order by ColA ASC;

    // order by ColA DESC;

     

     

    drop table BurnDownDates;

     

     

     

     

     

     

     

     

     

     

    /*

    // Inline Load, Sort Resident, Drop Original

    BurnDownDates:

    LOAD * INLINE

    [

        A1, A2

        1, 2011-37

        2, 2011-38

        3, 2011-39

        4, 2011-40

        5, 2011-41

        6, 2011-42

        7, 2011-43

        8, 2011-44

        9, 2011-45

        10, 2011-46

        11, 2011-47

        12, 2011-48

        13, 2011-49

        14, 2011-50

        15, 2011-51

        16, 2011-52

        17, 2012-1

        18, 2012-2

        19, 2012-3

        20, 2012-4

        21, 2012-5

        22, 2012-6

        23, 2012-7

        24, 2012-8

        25, 2012-9

        26, 2012-10

        27, 2012-11

        28, 2012-12

        29, 2012-13

        30, 2012-14

        31, 2012-15

        32, 2012-16

        33, 2012-17

        34, 2012-18

        35, 2012-19

        36, 2012-20

        37, 2012-21

        38, 2012-22

        39, 2012-23

        40, 2012-24

        41, 2012-25

        42, 2012-26

        43, 2012-27

        44, 2012-28

        45, 2012-29

        46, 2012-30

        47, 2012-31

        48, 2012-32

        49, 2012-33

        50, 2012-34

        51, 2012-35

        52, 2012-36

        53, 2012-37

        54, 2012-38

        55, 2012-39

        56, 2012-40

        57, 2012-41

        58, 2012-42

        59, 2012-43

        60, 2012-44

        61, 2012-45

        62, 2012-46

        63, 2012-47

        64, 2012-48

        65, 2012-49

        66, 2012-50

        67, 2012-51

        68, 2012-52

        69, 2013-1

        70, 2013-2

        71, 2013-3

        72, 2013-4

        73, 2013-5

        74, 2013-6

        75, 2013-7

        76, 2013-8

        77, 2013-9

        78, 2013-10

        79, 2013-11

        80, 2013-12

     

     

    ];

     

     

    Sorted:

    LOAD

    A1 As IdVal,

    A2 As YrWk

    resident BurnDownDates

    order by A1 ASC;

    // order by A1 DESC;

     

     

    drop table BurnDownDates

     

     

    */

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Stefan,

     

    After I loaded the dimension file and sorted in qlikview and dropped the prior table, I still have the same issue, 32 for the aggr statement and the row is really 44. ugh.... other ideas?

     

    Thanks,

     

    Don

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Will put all the rows that exist in the data in the dimension table, I see there is a mismatch.  Maybe that is what was meant.

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Nope, that was not it.  Stefan, which table is wrong.  The dimension YrWk is sorted now and has values starting the same place as the data, but same result:

     

    This expression gives me the rowno for YrWk=2012-5 as 44 which is correct.

     

    =if(YrWk='2012-5', RowNo(),0)                                                    // gives me 44 for that row

     

    This expression that makes that number available on all rows gives an incorrect answer of 32 now, you see what I am doing wrong:

     

    =Max(TOTAL aggr(NODISTINCT if(YrWk='2012-5', RowNo(),0)   ,YrWk)  )   

     

    Where is it getting the 32 from?

    • Re: How to have the Max RowNo from an expression available for all rows?
      swuehl

      Well I think I have lost track on what is going on in your app after all...

       

      YrWk is a key field, the load order is also determined by the order your ALL tables are loaded in (from file daily_dump_commulated.xls)

      From all I can see, the load order here is not sorted according a chronological ascending way.

       

      If I sort the list box for field YrWk by load order I get ( using your old posted app):

      2011-15

      2011-14

      2011-16

      2011-17

      2011-18

      2011-19

      2011-20

      2011-21

      2011-22

      2011-23

      2011-24

      2011-25

      2011-26

      2011-27

      2011-28

      2011-29

      2011-30

      2011-31

      2011-32

      2011-33

      2011-34

      2011-35

      2011-36

      2011-37

      2011-38

      2011-39

      2011-40

      2011-41

      2011-42

      2011-43

      2011-44

      2011-45

      2011-46

      2012-3

      2012-4

      2012-5

      2011-51

      2011-52

      2012-1

      2012-2

      2011-47

      2011-48

      2011-49

      2011-50

      2012-6

      2012-7

      2012-8

      2012-9

      2012-10

      2012-11

      2012-12

      2012-13

      2012-14

      2012-15

      2012-16

      2012-17

      2012-18

      2012-19

      2012-20

      2012-21

      2012-22

      2012-23

      2012-24

      2012-25

      2012-26

      2012-27

      2012-28

      2012-29

      2012-30

      2012-31

      2012-32

      2012-33

      2012-34

      2012-35

      2012-36

      2012-37

      2012-38

      2012-39

      2012-40

      2012-41

      2012-42

      2012-43

      2012-44

      2012-45

      2012-46

      2012-47

      2012-48

      2012-49

      2012-50

      2012-51

      2012-52

      2013-1

      2013-2

      2013-3

      2013-4

      2013-5

      2013-6

      2013-7

      2013-8

      2013-9

      2013-10

      2013-11

      2013-12

       

      And if I select brow, webe (first number is to indicate row number):

      1  2011-18

      2  2011-19

      3  2011-20

      4  2011-21

      5  2011-22

      6  2011-23

      7  2011-24

      8  2011-25

      9  2011-26

      10 2011-27

      11 2011-28

      12 2011-29

      13 2011-30

      14 2011-31

      15 2011-32

      16 2011-33

      17 2011-34

      18 2011-35

      19 2011-36

      20 2011-37

      21 2011-38

      22 2011-39

      23 2011-40

      24 2011-41

      25 2011-42

      26 2011-43

      27 2011-44

      28 2011-45

      29 2011-46

      30 2012-3

      31 2012-4

      32 2012-5

      33 2011-51

      34 2011-52

      35 2012-1

      36  2012-2

      37 2011-47

      38 2011-48

      39 2011-49

      40 2011-50

      41 2012-6

      42 2012-7

       

      That's why you get rowno() 32 if using the aggr() function.

       

      Not sure what you changed in the meantime, maybe you could post an updated version of your app?

      • Re: How to have the Max RowNo from an expression available for all rows?
        doschafe

        Stefan,

         

        Hmmm…  You are not talking about the dimension table but the non dimension tables such as All.  Those were created and are used by people long before me.  Not sure if I can change the order of those for I believe they use the order for some calculations, one of which may be the open calculation I am depending upon…

         

        Let me take a look.

         

        Thanks….

         

        Don

        • Re: How to have the Max RowNo from an expression available for all rows?
          swuehl

          I think I have mentioned the create-the-field-values-upfront-thing.

           

          I believe if you create all values of the field YrWk upfront, first in your load script, this will determine the load order.

          Even if you then load other tables with that field any unsorted values, the initial load order will be preserved (even after finally dropping the table). Like

           

          Autogen:

          LOAD *, year(WeekDate)&'-'&week(WeekDate) as YrWk;

          LOAD

          date(MakeWeekDate(2011,46+recno())) as WeekDate

          autogenerate 15;

           

          INPUT:

          LOAD * INLINE [

          YrWk

          2012-3

          2012-4

          2012-5

          2011-51

          2011-52

          2012-1

          2012-2

          2011-47

          2011-48

          2011-49

          2011-50

          2012-6

          2012-7

          2012-8

          2012-9

          ];

           

          drop table Autogen;

           

          The INPUT table is just a replacement as demo for your ALL tables etc. LOAD.

           

          I assume that you really need to get the rowno() from within your aggr() function, or in other words, that the thing you need to do with the rowno() can't be done in a different way.

           

          Regards,

          Stefan

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Stefan,

     

    Got a chance to take a look at this this afternoon and read your mail slowly. 

     

    It seemed the thing you were recommending that I did not grasp earlier was simply to move the load of my customdimension to the front of the load script.   I made that change and sure enough, it worked, now my row references work w/ aggr for that case and in general, excellent.....

     

    Thanks for repointing out the fact that moving the dimension load to the beginning of the load script may be all that is needed, very sweet and simple.

     

    D

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Stefan,

     

    My prior dialog indicates things worked.  There was an error in the data at that point.  The data was corrected and when I reloaded, the same issue appeared again.  I looked at the sort of the dimension in the table and indeed it is now sorted correctly but the answer is still not expected:

     

    This expression gives me the rowno for YrWk=2012-5 as 44 which is correct.

     

    =if(YrWk='2012-5', RowNo(),0)                                                    // gives me 44 for that row

     

    This expression that makes that number available on all rows gives an incorrect answer of 36 now, you see what I am doing wrong:

     

    =Max(TOTAL aggr(NODISTINCT if(YrWk='2012-5', RowNo(),0)   ,YrWk)  )  

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Stefan,

     

    I believe this is working in the general case.  I have input boxes that link to expressions and regardless of the values used, works fine.

     

    I believe the issue occurrs when Filtering is done.  This did not impact the last current row but does impact this expression.  Any way around it, the following still gives different answers if a selection filter is applied?

     

    =Max({1} TOTAL aggr(NODISTINCT if(YrWk='2012-6', RowNo(),0)   ,YrWk)  ) 

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Yes............. Excellent, thanks....

  • Re: How to have the Max RowNo from an expression available for all rows?
    doschafe

    Stefan,

     

    I put the expression in a variable and was trying to use on each row.  The interesting thing is that it does not allow me to use in basic math, even things such as:

     

    =$(StartBurnDownRowV)+2

     

    Do you see what I am missing?  I have used in calcs before, like the LastCurrentRow calc. 

     

    Thanks,

     

    D

    • How to have the Max RowNo from an expression available for all rows?
      swuehl

      But you get the correct number in each row when using the expression without adding some constant?

       

      =$(StartBurnDownRowV)

       

      Any difference when using in a textbox?

      • Re: How to have the Max RowNo from an expression available for all rows?
        doschafe

        Stefan

         

        Strange.

        vStartBurndownRowV=45

         

        This correctly calculates 55i:

         

        =10+$(vStartBurndownRowV)

         

        This incorrectly calculates 45:

         

        =$(vStartBurndownRowV)+10

         

        This gives me no answer:

         

        =($(vStartBurndownRowV))+10

         

         

        I must be missing something… Of course, I am trying to use the field with a calc like below for example:

         

        (

            If(RowNo()=$(vStartBurndownRowV),  $(vOpenErrorsTbaTbc),

                      If(RowNo()>$(vStartBurndownRow), ABOVE( $(vOpenErrorsTbaTbc), RowNo()-$(vStartBurndownRow))                                      // make variable when startburndown row calc issue resolved

           ))

         

        )

        • How to have the Max RowNo from an expression available for all rows?
          swuehl

          Sorry, no idea yet.

           

          Maybe you should open a new thread, so you get more attention on this new, unresolved problem from the community members.

           

          Have a nice weekend,

          Stefan

          • Re: How to have the Max RowNo from an expression available for all rows?
            doschafe

            Ok, I will.  I am currently replacing the variable with the expression to see if that gets me further.

          • Re: How to have the Max RowNo from an expression available for all rows?
            doschafe

            Stefan,

             

            The long hand version gives an expected answer:

             

            = If

            (

                           RowNo()=  (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

                           , $(vOpenErrorsTbaTbc)

                           ,If(  RowNo()>  (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

                                           , ABOVE( $(vOpenErrorsTbaTbc), RowNo()- (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )              )

            ))

             

             

            Where the one using variables gives null:

            (

                If(  RowNo()= $(vStartBurndownRowV),  $(vOpenErrorsTbaTbc),

                                            If(RowNo()>$(vStartBurndownRow), ABOVE( $(vOpenErrorsTbaTbc), RowNo()-$(vStartBurndownRow))

             

               ))

             

            )

             

            Hmmmm.

             

            Don

            • How to have the Max RowNo from an expression available for all rows?
              swuehl

              You could check how the dollar sign expansion is done clearing the label of the expression and then hover with your mouse over the label in your table (this should give the expression after dollar sign expansion).

               

              Regards,

              Stefan

              • Re: How to have the Max RowNo from an expression available for all rows?
                doschafe

                Stefan,

                 

                Not sure what you mean, will try a few other things.

                 

                Note, this is the fully exploded expression that does the trick however:

                =

                  (

                                If

                                (

                                               RowNo()=  (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

                                               , $(vOpenErrorsTbaTbc)

                                                               ,If (  RowNo()>  (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

                                                                               , ABOVE( $(vOpenErrorsTbaTbc), RowNo()- (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )              )

                                                                                )

                                )

                  )

                -

                  (

                    rangesum

                                                                (

                                                                                ABOVE(

                                                                                                If(RowNo()= (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

                                                                                                ,$(vOpenErrorsTbaTbc)/$(vNumberBurnDownWeeks)

                                                                                                , If(RowNo()> (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

                                                                                                , ABOVE( $(vOpenErrorsTbaTbc)/$(vNumberBurnDownWeeks), RowNo()- (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    ))

                                                                                                )

                                                                                  )

                                                                                                , 0

                                                                                                , RowNo() - (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

                                                                                                )

                                                                )

                   )

                • How to have the Max RowNo from an expression available for all rows?
                  swuehl

                  Not sure if this is related to your problem in any way, but you may wanna have a look into

                   

                  http://www.qlikfix.com/2011/06/08/not-all-variables-are-created-equal/

                   

                  which is describing the different evaluation of variables (similar to using Set / Let statements in the script).

                   

                  What I meant with checking the dollar sign expansion is related to this. Sometimes it's really enlightening if you see the expression after the dollar sign expansion or variable replacement took place.

                  One way to see this if to look at the header in your table, if no label in expression tab is set. The header will then just show the expression, but after the dollar sign expansion took place. So you can actually see, what is replacing the variable. Maybe this gives you any insight to your problem, not sure though.