I have a Internados table with [ID internado], [Patient] REF and a virtual referenced column REF_ROWS("Tratamientos", "Paciente"). I order it in UX by date descending. Tratamientos is the orange symbol.
[Tratamientos] table has ([ID Tratamiento], [Paciente], [Antibiótico] and [Fecha Inicio]. Fecha Inicio = start date.
I created another view with table "Internados" called "Pase de sala". And i want to show the latest [Antibiotico] by Patient
I created a new virtual column ATB and SELECT(Tratamientos[Antibiotico],[Paciente]=[_thisrow].[ID Internado])
But as you see in the third picture isn´t ordered by date. It must be Colistin, Imipenem, Ceftriaxona, Tazonam.
Hope you can help me.
Solved! Go to Solution.
Hi @mdibiasio
"And I want to show the latest [Antibiotico] by Patient"
What about:
1) creating a virtual column [orderedTratamientos], with expression:
ORDERBY(
[Related Tratamientos],
[Fecha Inicio],
TRUE
)
2) creating another virtual column [VC_ATB] with expression:
[orderedTratamientos][Antibiotico]
3) having the last antibiotico for this patient:
ANY(TOP(
ANY(
TOP([orderedTratamientos][Antibiotico],1)
)
or
INDEX([orderedTratamientos][Antibiotico],1)
For reference:
Build list dereferences - AppSheet Help
Creo que con una formula parecida a la siguiente puedes obtener: El antibiotico con la fecha de inicio más reciente por paciente.
SELECT(Tratamientos[Antibiotico],AND(
[Paciente]=[_thisrow].[Paciente],
[ID Tratamiento] = ANY([Tratamientos]),
[Fecha Inicio]= MAX(SELECT(Tratamientos[Fecha Inicio], [ID Tratamiento] = ANY([Tratamientos] )))))
La formula anterior suponiendo que a [Related Tratamientos] le cambiaste el nombre a [Tratamientos]
Ahi me cuentas si te funcionó porque la verdad sólo estoy suponiendo que te puede funcionar.
Otra que se me ocurrio después:
SELECT(Tratamientos[Antibiotico],[ID Tratamiento]=INDEX(ORDERBY([Related Tratamientos],[Fecha Inicio],True),1))
Yes, [Tratamientos]=[Related Tratamientos] but Open and close Parenthesis does not match in neither of them
indeed, parentheses are needed at the end of the formulas. I already edited the comment in case you are curious to use any of the formulas
Hi @mdibiasio
"And I want to show the latest [Antibiotico] by Patient"
What about:
1) creating a virtual column [orderedTratamientos], with expression:
ORDERBY(
[Related Tratamientos],
[Fecha Inicio],
TRUE
)
2) creating another virtual column [VC_ATB] with expression:
[orderedTratamientos][Antibiotico]
3) having the last antibiotico for this patient:
ANY(TOP(
ANY(
TOP([orderedTratamientos][Antibiotico],1)
)
or
INDEX([orderedTratamientos][Antibiotico],1)
For reference:
Build list dereferences - AppSheet Help
I create the first step in Internados table
But in the second step is unable to find column [orderedTratamientos]
Did you click on the big blue button "Save" after the first step ?
User | Count |
---|---|
40 | |
34 | |
28 | |
23 | |
17 |