Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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 ,
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
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
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;
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;