Help in script - using date , rowno and order by desc

[ashmitp869]

Hi there,

Please help me out with the script -

LOAD
DISTINCT Date(Date#([Today's date],'M/D/YYYY'),'D/M/YYYY') as _Date,
RowNo() as WeekN,
"COVID +ve patients in ICU - primarily receiving treatment for COVID" as "ICU COVID (Primary)",
"COVID +ve patients in ICU – for other reasons" as "ICU COVID (Incidental)",
FROM [lib:/Weekly COVID numbers - testing.xlsx]
(ooxml, embedded labels, table is Form1) order by desc;

I have to create WEEKN variable -

It should be

WEEKN as 0 for this week-29/03

WEEKN as 1 for this week-22/03

WEEKN as 2 for this week-15/03

Please help me out with the WEEKN variable how to take distinct Today_date and then put the rowno() in desc order.

[ashmitp869]

Hi there ,

T1:

LOAD
Date(Date#([Today's date],'M/D/YYYY'),'D/M/YYYY') as _Date,
"COVID +ve patients in ICU - primarily receiving treatment for COVID" as "ICU COVID (Primary)",
"COVID +ve patients in ICU – for other reasons" as "ICU COVID (Incidental)",
FROM [lib:/Weekly COVID numbers - testing.xlsx]
(ooxml, embedded labels, table is Form1) ;

T2:
Load
Distinct _Date,
RowNo() as WeekN
Resident T1;

Why I am still getting 10 Rows , 

[rwunderlich]

Your chart fieldname does not match the script you posted, nor do you show dropping T1 in your script. Could you post the actual script?

-Rob

[MarcoWedel · Accepted solution]

I think the RowNo() counteracts the distinct in this case.

So you either need an intermediate step to distinct the _Date values before applying the RowNo() function or you just replace "RowNo() as WeekN" with "AutoNumber(_Date) as WeekN".

hope this helps

Marco

[ashmitp869]

Hi Marco,

Thanks for the AutoNumber(_Date) as WeekN, you are correct RowNo() counteracts with disctinct case.

Can you help me out with the an intermediate step to distinct the _Date values before applying the RowNo() function  ?

Here is my script -

qualify *;

unqualify _Date,"Facility Identifier","FacilityCode-Date";

WeeklyCovidNo:

LOAD

Date(Date#([Today's date],'M/D/YYYY'),'D/M/YYYY') as  _Date,   

"Facility Identifier",

 [FacilityCode-Date],

"COVID +ve patients in ICU - primarily receiving treatment for COVID" as "ICU COVID (Primary)",

"COVID +ve patients in ICU – for other reasons" as  "ICU COVID (Incidental)",

"COVID +ve patients in wards – in hospital primarily receiving treatment for COVID" as  "Wards COVID (Primary)",

"COVID +ve patients in wards - in hospital primarily for non-COVID reasons"  as  "Wards COVID (Incidental)"

FROM [lib://QVD COVID19/SitRep/Weekly COVID numbers1.xlsx]

(ooxml, embedded labels, table is Form1) ;

T2:

Load

Distinct _Date,

AutoNumber(_Date) as WeekN

Resident WeeklyCovidNo;

FinalTable:

left join(WeeklyCovidNo)

LOAD 

_Date,

T2.WeekN as WeeklyCovidNo.WeekN

Resident T2;

drop table T2;

[ashmitp869]

Hi Rob,

Here is my script

qualify *;

unqualify _Date,"Facility Identifier","FacilityCode-Date";

WeeklyCovidNo:

LOAD

Date(Date#([Today's date],'M/D/YYYY'),'D/M/YYYY') as  _Date,   

"Facility Identifier",

 [FacilityCode-Date],

"COVID +ve patients in ICU - primarily receiving treatment for COVID" as "ICU COVID (Primary)",

"COVID +ve patients in ICU – for other reasons" as  "ICU COVID (Incidental)",

"COVID +ve patients in wards – in hospital primarily receiving treatment for COVID" as  "Wards COVID (Primary)",

"COVID +ve patients in wards - in hospital primarily for non-COVID reasons"  as  "Wards COVID (Incidental)"

FROM [lib://QVD COVID19/SitRep/Weekly COVID numbers1.xlsx]

(ooxml, embedded labels, table is Form1) ;

T2:

Load

Distinct _Date,

AutoNumber(_Date) as WeekN

Resident WeeklyCovidNo;

FinalTable:

left join(WeeklyCovidNo)

LOAD 

_Date,

T2.WeekN as WeeklyCovidNo.WeekN

Resident T2;

drop table T2;