Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need to have a check which return if a value is null or not.
if it is null, need to get sum of different values.
if the value exists so we just need to bring it.
attached is the table with example :
T1:
* INLINE [
Mapping, Data
A, 10
B, 20
C, 30
E, 40
F, 50
G, 60
];
LOAD
///////////// ************************************************ check if c exists ( c= a+b)
IF (ISNULL(WildMatch(Mapping ,'C'))= 0 ,0, SUM(Data)) AS C,
Mapping
resident T1
Where 1 AND
WildMatch(Mapping,'A','B','C')
Group BY
Mapping ;
LOAD
///////////// *********************************************** check if H exists ( H= a+b+f+g)
IF (ISNULL(WildMatch(Mapping ,'H'))= 0 ,0, SUM(Data)) AS H,
Mapping
resident T1
Where 1 AND
WildMatch(Mapping,'H','B','F','G')
Group BY
Mapping ;
Thanks
Roee
Hi,
You can use isnull(fielName) or len(trim(fieldName))=0
for example:
If(isnull(fieldName),AnotherFieldName,FieldNAme) as New_fielName
Regards
Hi Max,
thank you for your quick answer .
I tried and it doesn't work.
LOAD
if(len(trim(WildMatch(Mapping,'H')))= 0,sum(Data)) AS XXX,
Mapping
resident T1
Where 1 AND
WildMatch(Mapping,'H','B','F','G')
Group BY
Mapping ;
As per my understanding, please find the solution below:
data0:
LOAD * INLINE [
Mapping, MapID
A, 1
B, 2
C, 3
D, 4
E, 5
F, 6
G, 7
H, 8
];
data1:
Left join(data0)
LOAD * INLINE [
Mapping, Data
A, 10
B, 20
C, 30
E, 40
F, 50
G, 60
]
where Exists(Mapping);
data3:
LOAD
MapID,
Mapping,
Data,
NUMSUM(PEEK('_tmp_Data'),Data) AS _tmp_Data
RESIDENT data0 ORDER BY MapID;
DROP TABLE data0;
final:
LOAD
MapID,
Mapping,
Data,
_tmp_Data,
IF(IsNull(Data),_tmp_Data,Data) As FinalData
RESIDENT data3 ;
DROP TABLE data3;
//Use: Mapping , FinalData as fields in your tablebox
Thanks,
Angad
Can you post expected output