Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

34 Replies
Not applicable
Author

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

Not applicable
Author

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;

Not applicable
Author

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

*/

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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?

swuehl
MVP
MVP

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?

Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

Stefan,

Thanks…. Am not grasping fully yet. Got pulled to another task for today so will not be able to focus on this even though I would like to consider your approach. Let me focus on my different new task and then come back to this. As a result, my next question may not be today.

Don