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

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

ashmitp869 — Tue, 29 Mar 2022 06:32:53 GMT

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 .

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.

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

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

dmac1971 — Tue, 29 Mar 2022 07:38:47 GMT

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

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

ashmitp869 — Wed, 30 Mar 2022 04:44:33 GMT

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().

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

dmac1971 — Wed, 30 Mar 2022 07:08:09 GMT

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

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

ashmitp869 — Wed, 30 Mar 2022 07:34:41 GMT

then how to solve new year -

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

Please assist.