Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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
*/
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
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.
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?
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?
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
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
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