Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How do i group rows when i cant group on one field? the reason is shown underneath, sometimes thers no value in some of the field but the row has a value for exampel phone thats connect it to a nother row there i can find ex. code.
code | phone | X1 | X2 | |
---|---|---|---|---|
13 | 13@13.13 | |||
13 | 077113 | |||
077113 | 13@13.13 | |||
13@13.13 | ||||
12 | 077112 | 12@12.12 | ||
12 | 12@12.12 | |||
12 | 077112 | |||
16 | 16@16.16 | |||
16 | 077116 | |||
16 | 077116 | 16@16.16 | ||
077116 | 16@16.16 |
The wanted result is:
code | phone | X1 | X2 | |
---|---|---|---|---|
13 | 077113 | 13@13.13 | ||
12 | 077112 | 12@12.12 | ||
16 | 077116 | 16@16.16 | ||
Think its a no brainer but i cant get it right. Any ideas?
In a straight table you can use code as dimension and expressions like maxstring(phone) and maxstring(mail). And enable Suppress When Value is Null for the code dimension.
Okej, but i want to do it in the load script. Is it possible?
Something like:
Load code, maxstring(phone) as phone, maxstring(mail) as mail
from ...somewhere...
group by code;
dose that realy work on:
code | phone | X1 | X2 | |
---|---|---|---|---|
13 | ||||
13 | 077113 | |||
077113 | 13@13.13 |
Yes, it works perfectly. It does not make any guessed but works with the data given. Nulls stay nulls and are not replaced by random values or values that would result from an algorithm that is not specified.
If it does not do what you want, then you did not specify what should happen.
My script:
SET NULLINTERPRET=NULL;
Dev:
LOAD *
Inline [
code, phone, mail,
'13',NULL,NULL
'13','077113',NULL
NULL,'077113','13@13.13'
];
Dev2:
load
code
,MaxString(phone) as phone
,MaxString(mail) as mail
Resident Dev
Group By code;
DROP Table Dev;
Makes:
code | phone | X1 | X2 | |
---|---|---|---|---|
13 | 077113 | |||
077113 | 13@13.13 |
My goal:
code | phone | X1 | X2 | |
---|---|---|---|---|
13 | 077113 | 13@13.13 |
Hi,
Try like this
Dev:
LOAD *
Inline [
code, phone, mail,
'13',NULL,NULL
'13','077113',NULL
NULL,'077113','13@13.13'
];
Dev2:
load
code
,MaxString(phone) as phone
,MaxString(mail) as mail
Resident Dev
WHERE Not IsNull(phone) or Not IsNull(mail) // This removes Null records
Group By code;
DROP Table Dev;
Regards,
Jagan.
HI Marten,
I have created the Sample App based on your data and applied same logic which gwassenaar had mentioned through UI/Script.
find the attachment
Outputs
code | phone | X1 | X2 | |
---|---|---|---|---|
13 | 077113 | |||
077113 | 13@13.13 |
My goal
code | phone | X1 | X2 | |
---|---|---|---|---|
13 | 077113 | 13@13.13 |