Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day!
Colleagues, tell me please, has QV some function to get index number of values in some field (not in load script)
Model and necessary WeekNumber in attached file.
Thanks.
If you can change load script, use recno(), else try to use expression in my early answer.
No function to calculate index number field that can be used in expression
ok. if i use recno(), it will return me number of each record.
For example:
YearWeek Sales RecNo()
201501 1 1
201501 67 2
201501 24 3
201502 24 4
I need:
YearWeek Sales RecNo()
201501 1 1
201501 67 1
201501 24 1
201502 24 2
Hi ,
In your load script itself you can make changes like this,
Data:
LOAD
Mid(YearWeek,1,4) as Year,
Mid(YearWeek,5,6) as week ,
YearWeek,
Sales
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Source1) where YearWeek>'201540';
Then in front end you ,
expression:
=Sum(Sales)
Hope this helps,
.PFA,
Hirish
so, you have records with repeatly YearWeek and some YearWeek can be missed?
How can we automatic determine number YearWeek - 14, we can't write 201540
for example use script
DATA:
LOAD YearWeek,
Sales
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Source1)
;
left join (DATA)
load Distinct YearWeek
, RowNo() as Row
Resident DATA;
and expression sum({<Row= {">$(=max(total Row)-14) "}>}Sales)
Distinct load doesn't give result
in xlsx two records for 201542 and 201547, and no records for 201550
Hope, it'll help
i've solved it:
DATA:
LOAD YearWeek,
Sales
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Source1)
;
YearWeeks_Temp:
load Distinct
YearWeek
Resident DATA;
YearWeeks:
load*,
RowNo() as WeekNumber
Resident YearWeeks_Temp
Order By YearWeek asc;
DROP Table YearWeeks_Temp;
Left Join(DATA)
load
*
Resident YearWeeks;
DROP Table YearWeeks;
I think it's not optimized