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: 
Anonymous
Not applicable

Week index number

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.

32 Replies
t_chetirbok
Creator III
Creator III

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
Not applicable
Author

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
Master
Master

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

HirisH
“Aspire to Inspire before we Expire!”
t_chetirbok
Creator III
Creator III

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

t_chetirbok
Creator III
Creator III

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

t_chetirbok
Creator III
Creator III

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
Not applicable
Author

Distinct load doesn't give result

t_chetirbok
Creator III
Creator III

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

Hope, it'll help

Anonymous
Not applicable
Author

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
Creator III
Creator III

I think it's not optimized