Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm trying to create dimension like "Range" below:
Hello,
one solution :
Data:
CrossTable(value, Data)
load * inline [
ID,value1,value2,value3,value4
xx,21,17,50,2,48
yy,31,0,12,40
zz,12,14,18,9
];
left join
load ID,Max(Data)-Min(Data) as Range resident Data group by ID;
output:
load distinct ID ,Range resident Data;
Tmp:
Generic LOAD ID,value,Data resident Data;
drop table Data;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
//trace $(i) - '$(vTable)';
IF WildMatch('$(vTable)', 'Tmp.*') THEN
LEFT JOIN ([output]) LOAD distinct * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
output :
one solution if number of Field is fixed :
load RangeMax(value1,value2,value3,value4)-RangeMin(value1,value2,value3,value4) as Range,* inline [
ID,value1,value2,value3,value4
xx,21,17,50,2,48
yy,31,0,12,40
zz,12,14,18,9
];
output :
Hello,
one solution :
Data:
CrossTable(value, Data)
load * inline [
ID,value1,value2,value3,value4
xx,21,17,50,2,48
yy,31,0,12,40
zz,12,14,18,9
];
left join
load ID,Max(Data)-Min(Data) as Range resident Data group by ID;
output:
load distinct ID ,Range resident Data;
Tmp:
Generic LOAD ID,value,Data resident Data;
drop table Data;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
//trace $(i) - '$(vTable)';
IF WildMatch('$(vTable)', 'Tmp.*') THEN
LEFT JOIN ([output]) LOAD distinct * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
output :
one solution if number of Field is fixed :
load RangeMax(value1,value2,value3,value4)-RangeMin(value1,value2,value3,value4) as Range,* inline [
ID,value1,value2,value3,value4
xx,21,17,50,2,48
yy,31,0,12,40
zz,12,14,18,9
];
output :
Excellent! Thank you. I like second solution and don't understand the first one. Is there any advantage of the first one?
the first one is good if the Fields are dynamic on like this
value1,value2,value3,value4
and another
value1,value2,value3,value4,value5,value6,...
Thank you again, it's very helpful. God bless you!
Now I have another issue with the same data. Let's say I have fixed no of fields (value1-value4). Now I need to calculate Standard Deviation StDev() on all values from fields value1,value2, value3 and value 4 but that function takes only one parameter... How can I collect all single values from 4 dimensions and create one set of values? What's more, values equal to 0 should be omitted.
I think in this case you can use the first version.
Data:
CrossTable(value, Data)
load * inline [
ID,value1,value2,value3,value4
xx,21,17,50,2,48
yy,31,0,12,40
zz,12,14,18,9
];
left join
load ID,StDev(Data) as STDEV resident Data where Data<>0 group by ID ;
output:
load distinct ID ,STDEV resident Data;
Tmp:
Generic LOAD ID,value,Data resident Data;
drop table Data;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
//trace $(i) - '$(vTable)';
IF WildMatch('$(vTable)', 'Tmp.*') THEN
LEFT JOIN ([output]) LOAD distinct * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
output :
Thank you for your prompt answer but that is not exactly what I described. I need only one StDev result. Not StDev for individual IDs. In this case it will be around 14,86
In this case I think you need just to add CrossTable , like :
Data:
CrossTable(value, Data)
load * inline [
ID,value1,value2,value3,value4
xx,21,17,50,2,48
yy,31,0,12,40
zz,12,14,18,9
];
and in the interface KPI :
=StDev(Data)
output :
Dear Taoufiq,
thank you but.. How to exclude 0 here? I don't want to treat 0 as a value.
I would like it to be also subjected to selections for example I may need to choose only xx and yy IDs.