Forum Discussion
Query comparing continuous variables
Hi sminonese,
You can refer to bleow steps if it suitable for your requirement:
1. Use "SELECTCOLUMNS" and "CONCATENATEX" function to add the quality tag.
Summary Table = SELECTCOLUMNS('Sample',"SampleID",[SampleID],"Quality",CONCATENATEX(FILTER(ALL('Quality Specifications'),[Quant1]>=[Quant1_Low]&&[Quant1]<=[Quant1_high]&&[Quant2]>=[Quant2_Low]&&[Quant2]<=[Quant2_high]),[Quality],","))2. Use variable to store above table and use SUBSTITUTE function to convert "Quality" column to new columns.
Summary Table = var temp=SELECTCOLUMNS('Sample',"SampleID",[SampleID],"Quality",CONCATENATEX(FILTER(ALL('Quality Specifications'),[Quant1]>=[Quant1_Low]&&[Quant1]<=[Quant1_high]&&[Quant2]>=[Quant2_Low]&&[Quant2]<=[Quant2_high]),[Quality],",")) return SELECTCOLUMNS(temp,"SampleID",[SampleID],"LEFT", LEFT(SUBSTITUTE([Quality],",","-"),SEARCH("-",SUBSTITUTE([Quality],",","-"))-1),"RIGHT",RIGHT(SUBSTITUTE([Quality],",","-"),LEN(SUBSTITUTE([Quality],",","-"))-SEARCH("-",SUBSTITUTE([Quality],",","-"))))3. Use SELECTCOLUMNS and UNION function to merge left and right part.
Summary Table = var temp=SELECTCOLUMNS('Sample',"SampleID",[SampleID],"Quality",CONCATENATEX(FILTER(ALL('Quality Specifications'),[Quant1]>=[Quant1_Low]&&[Quant1]<=[Quant1_high]&&[Quant2]>=[Quant2_Low]&&[Quant2]<=[Quant2_high]),[Quality],",")) return UNION( SELECTCOLUMNS(temp,"SampleID",[SampleID],"Quality", LEFT(SUBSTITUTE([Quality],",","-"),SEARCH("-",SUBSTITUTE([Quality],",","-"))-1)), SELECTCOLUMNS(temp,"SampleID",[SampleID],"Quality",RIGHT(SUBSTITUTE([Quality],",","-"),LEN(SUBSTITUTE([Quality],",","-"))-SEARCH("-",SUBSTITUTE([Quality],",","-")))) )Regards,
Xiaoxin Sheng
Hi sminonese,
You can refer to bleow steps if it suitable for your requirement:
1. Use "SELECTCOLUMNS" and "CONCATENATEX" function to add the quality tag.
Summary Table = SELECTCOLUMNS('Sample',"SampleID",[SampleID],"Quality",CONCATENATEX(FILTER(ALL('Quality Specifications'),[Quant1]>=[Quant1_Low]&&[Quant1]<=[Quant1_high]&&[Quant2]>=[Quant2_Low]&&[Quant2]<=[Quant2_high]),[Quality],","))
2. Use variable to store above table and use SUBSTITUTE function to convert "Quality" column to new columns.
Summary Table =
var temp=SELECTCOLUMNS('Sample',"SampleID",[SampleID],"Quality",CONCATENATEX(FILTER(ALL('Quality Specifications'),[Quant1]>=[Quant1_Low]&&[Quant1]<=[Quant1_high]&&[Quant2]>=[Quant2_Low]&&[Quant2]<=[Quant2_high]),[Quality],","))
return
SELECTCOLUMNS(temp,"SampleID",[SampleID],"LEFT", LEFT(SUBSTITUTE([Quality],",","-"),SEARCH("-",SUBSTITUTE([Quality],",","-"))-1),"RIGHT",RIGHT(SUBSTITUTE([Quality],",","-"),LEN(SUBSTITUTE([Quality],",","-"))-SEARCH("-",SUBSTITUTE([Quality],",","-"))))
3. Use SELECTCOLUMNS and UNION function to merge left and right part.
Summary Table =
var temp=SELECTCOLUMNS('Sample',"SampleID",[SampleID],"Quality",CONCATENATEX(FILTER(ALL('Quality Specifications'),[Quant1]>=[Quant1_Low]&&[Quant1]<=[Quant1_high]&&[Quant2]>=[Quant2_Low]&&[Quant2]<=[Quant2_high]),[Quality],","))
return
UNION(
SELECTCOLUMNS(temp,"SampleID",[SampleID],"Quality", LEFT(SUBSTITUTE([Quality],",","-"),SEARCH("-",SUBSTITUTE([Quality],",","-"))-1)),
SELECTCOLUMNS(temp,"SampleID",[SampleID],"Quality",RIGHT(SUBSTITUTE([Quality],",","-"),LEN(SUBSTITUTE([Quality],",","-"))-SEARCH("-",SUBSTITUTE([Quality],",","-"))))
)
Regards,
Xiaoxin Sheng
Thank you Xiaoxin! This is an excellent answer.
I have tweaked the code a little bit to allow tagging up to 3 "Quality"-tags and removing blanks. Additionally I noticed another tip to use PATHITEM to split into columns by the pipe delimiter, so I can skip the substitution step.
An improvement would be to have a default tag such as "Other/Offspec" to fall back when there is no matching Quality spec. A second improvement would be to support arbitrary number of tags, right now only supporting up to 3.
QualityTags =
var temp = SELECTCOLUMNS(Samples;"SampleID";[SampleID];"Quality";CONCATENATEX(
FILTER(
ALL('Quality Specifications');
[Quant1]>=[Quant1_Low]&&
[Quant1]<=[Quant1_High]&&
[Quant2]>=[Quant2_Low]&&
[Quant2]<=[Quant2_High]
);
[Quality];"|"))
return
UNION(
FILTER(
SELECTCOLUMNS(temp;"SampleID";[SampleID];
"Quality";PATHITEM([Quality];1)
);
[Quality1]<>BLANK()
);
FILTER(
SELECTCOLUMNS(temp;"SampleID";[SampleID];
"Quality2";PATHITEM([Quality];2)
);
[Quality2]<>BLANK()
);
FILTER(
SELECTCOLUMNS(temp;"SampleID";[SampleID];
"Quality3";PATHITEM([Quality];3)
);
[Quality3]<>BLANK()
)
)