Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
ashmitp869
Creator II
Creator II

Use of Row() to get WeekN for distinct date - script - not working

Hi there,

How to use RowNo() in script to get the WeekN automatically in Qlik App 

I have a excel file like below with other fields which is manual entry. My req is to make WeekN to be automatic i,e without the data entry .

ashmitp869_0-1648535228483.png

I am using the below script - 

 

WeeklyCovidNo1:
LOAD
MakeDate(Year([Today's date]),Month([Today's date]), Day([Today's date])) as _Date,
"COVID +ve patients in ICU",
"COVID +ve patients in ICU ,
"COVID +ve patients in wards ",
"COVID +ve patients in wards"
FROM [lib:/testing.xlsx]
(ooxml, embedded labels, table is Form1);

WeeklyCovidNo:

noconcatenate
Load
Distinct _Date
,
RowNo() as WeekN
Resident WeeklyCovidNo1 ;

Drop table WeeklyCovidNo1;

 

But I am getting like below- each entry with no.

ashmitp869_1-1648535485811.png

 

Please kindly assist me with the script - So that WeekN is calculated automatically.

 

Labels (1)
4 Replies
dmac1971
Creator III
Creator III

This should work?  (Week(Today())-Week(Date))-1 As WeekN

ashmitp869
Creator II
Creator II
Author

Hi there,

this is woking but it won't work for new year.

It restarted to be week 1. Then 1-52=-51.

Can you help me how to get distinct date and then count the rowno().

 

 

dmac1971
Creator III
Creator III

RowNo() probably isnt the right approach here.  You should be easily able to adapt the Week approach?

ashmitp869
Creator II
Creator II
Author

then how to solve new year -

It restarted to be week 1. Then 1-52=-51.

Please assist.