Week index number

[Anonymous]

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.

[t_chetirbok]

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

[Anonymous]

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

[HirisH_V7]

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

[t_chetirbok]

so, you have records with repeatly YearWeek and some YearWeek can be missed?

[t_chetirbok]

How can we automatic determine number YearWeek - 14, we can't write 201540

[t_chetirbok]

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)

[Anonymous]

Distinct load doesn't give result

[t_chetirbok]

in xlsx two records for 201542 and 201547, and no records for 201550

Hope, it'll help

[Anonymous]

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;

[t_chetirbok]

I think it's not optimized