Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
my expression:
If((Min(MONTHYEAR) > Min(MONTHYEAR2)) and len(trim(FIELD1)<>0), Only(FIELD1B),FIELD1) as Value
What I doing wrong?
Give this a shot
If((Min(MONTHYEAR) > Min(MONTHYEAR2)) and len(trim(FIELD1))>0, Only(FIELD1B),FIELD1) as Value
Sorry, invalid expression...
Thanks!
Hi,
Try the expressions separately without the AND condition and check it out. If everything works fine then move ahead with AND condition. Could you post your sample app?
Code:
Table:
LOAD *,
If(Len(Trim(MonthName(MakeDate(YEAR, MONTH)))) = 0, MonthName(MakeDate(YEAR2, MONTH2)), MonthName(MakeDate(YEAR, MONTH))) as MONTHYEAR,
MonthName(MakeDate(YEAR2, MONTH2)) as MONTHYEAR2
Inline [
ID,MONTH,YEAR,FIELD1,FIELD2,MONTH2,YEAR2,FIELD1B
2,2,2005,10,B,3,2005,40
2,5,2005,40,D,,,40
23,11,2008,25,C,,,30
23,2,2009,30,H,,,30
100,,,,,6,2010,555
500,3,2015,80,X,1,2015,58
500,6,2015,60,W,,,58
333,4,2014,100,AA,8,2014,100
333,6,2014,300,BB,,,100
];
Join (Table)
LOAD ID,
Value,
MonthName(AddMonths(Min, IterNo()-1)) as MONTHYEAR
While AddMonths(Min, IterNo()) <= Max;
LOAD ID,
If(Max(MONTHYEAR) < Max(MONTHYEAR2), Alt(Max(MONTHYEAR2), Max(MONTHYEAR)), Max(MONTHYEAR)) as Max,
If(Min(MONTHYEAR) < Min(MONTHYEAR2), Min(MONTHYEAR), Alt(Min(MONTHYEAR2), Min(MONTHYEAR))) as Min,
//I Want to change this expression
If(Max(MONTHYEAR) < Max(MONTHYEAR2) or Min(MONTHYEAR) > Min(MONTHYEAR2), Only(FIELD1B)) as Value
//HERE I NEED:
/*
IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(FIELD1),FIELD1B,if(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(FIELD1)>0,FIELD1)) as Value
I need this:
1.-if(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(FIELD1) output FIELD1B
2.-if(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnotnull(FIELD1) output FIELD1
*/
Resident Table
Group By ID;
FinalTable:
NoConcatenate
LOAD ID,
MONTH,
YEAR,
MONTHYEAR,
Alt(Alt(FIELD1, FIELD1B), Value) as FIELD1,
FIELD2;
LOAD ID,
Num(Month(MONTHYEAR)) as MONTH,
Year(MONTHYEAR) as YEAR,
MONTHYEAR,
// MONTH2,
// YEAR2,
FIELD1B,
Value,
If(Len(Trim(FIELD1)) = 0, IF(ID = Previous(ID), Peek('FIELD1')), FIELD1) as FIELD1,
If(Len(Trim(FIELD2)) = 0, IF(ID = Previous(ID), Peek('FIELD2')), FIELD2) as FIELD2
Resident Table
Order By ID, MONTHYEAR;
DROP Table Table;
//////////////////////
You need aggregation functions for all fields that you don't list in your GROUP BY clause:
IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(Only(FIELD1)),Only(FIELD1B),if(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(Only(FIELD1))>0,Only(FIELD1))) as Value
or maybe just
IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(Only(FIELD1)),Only(FIELD1B),Only(FIELD1) ) as Value
Not sure if this is what you want, though. Probably Sunny can help you with the logic.
Try with nested IF's like below:
IF( Min(MONTHYEAR) > Min(MONTHYEAR2) ,
IF( Len(Trim(FIELD1)) , FIELD1 , FIELD1B
)
)
With this inline table:
Inline [
ID,MONTH,YEAR,FIELD1,FIELD2,MONTH2,YEAR2,FIELD1B
1000,3,2015,rev,bb,10,2014,soul
1000,5,2015,soul,rev,10,2014,soul
555,3,2015,50,40,2,2015,80
555,4,2015,80,50,2,2015,80
100,,,,,6,2010,555];
IF MONTH2<MONTH AND FIELD2 IS NOT NULL THEN FINAL_FIELD1=FIELD2
IF MONTH2<MONTH AND FIELD2 IS NULL THEN FINAL_FIELD1=FIELD1B
I only use FIELD1B if month2 and year2 are <month and year and FIELD2 is null
but i always use month2 and year2 for the table
Output:
ID,MONTH,YEAR,FINAL_FIELD1,FINAL_FIELD2
1000,10,2014,bb,-
1000,11.2014,bb,-
1000,12,2014,bb,-
1000,1,2015,bb,-
1000,2,205,bb,-
1000.3,2015,rev,bb
1000,4,2015,rev,bb
1000,5,2015,soul,rev
555,2,2015,40,-
555,3,2015,50,40
555,4,2015,80,50
100,6,2010,555
Thank you for your answers
My code now:
Table:
LOAD *,
If(Len(Trim(MonthName(MakeDate(YEAR, MONTH)))) = 0, MonthName(MakeDate(YEAR2, MONTH2)), MonthName(MakeDate(YEAR, MONTH))) as MONTHYEAR,
MonthName(MakeDate(YEAR2, MONTH2)) as MONTHYEAR2
Inline [
ID,MONTH,YEAR,FIELD1,FIELD2,MONTH2,YEAR2,FIELD1B
1000,3,2015,rev,bb,10,2014,soul
1000,5,2015,soul,rev,10,2014,soul
555,3,2015,50,40,2,2015,80
555,4,2015,80,50,2,2015,80
100,,,,,6,2010,555];
Join (Table)
LOAD ID,
Value,
MonthName(AddMonths(Min, IterNo()-1)) as MONTHYEAR
While AddMonths(Min, IterNo()) <= Max;
LOAD ID,
MonthName(addmonths(today(),-0)) as Max,
If(Min(MONTHYEAR) < Min(MONTHYEAR2), Min(MONTHYEAR), Alt(Min(MONTHYEAR2), Min(MONTHYEAR))) as Min,
IF(Min(MONTHYEAR) > Min(MONTHYEAR2) and isnull(Only(FIELD1)),Only(FIELD1B),if(Min(MONTHYEAR) > Min(MONTHYEAR2) and len(Only(FIELD1))>0,Only(FIELD1))) as Value
Resident Table
Group By ID;
FinalTable:
NoConcatenate
LOAD ID,
Value,
MONTH,
YEAR,
MONTHYEAR,
If( Len(Trim(FIELD1)), FIELD1, If( Len(Trim(FIELD1B)), FIELD1B, Value)) as FIELD1,
FIELD2;
LOAD ID,
Num(Month(MONTHYEAR)) as MONTH,
Year(MONTHYEAR) as YEAR,
MONTHYEAR,
FIELD1B,
Value,
If(Len(Trim(FIELD1)) = 0, IF(ID = Previous(ID), Peek('FIELD1')), FIELD1) as FIELD1,
If(Len(Trim(FIELD2)) = 0, IF(ID = Previous(ID), Peek('FIELD2')), FIELD2) as FIELD2
Resident Table
Order By ID, MONTHYEAR;
DROP Table Table;
But the output is:
ID | MONTH | YEAR | FIELD1 | FIELD2 | MONTHYEAR | Value |
---|---|---|---|---|---|---|
555 | 2 | 2015 | 80 | Feb 2015 | 80 | |
555 | 3 | 2015 | 50 | 40 | Mar 2015 | 80 |
555 | 4 | 2015 | 80 | 50 | Apr 2015 | 80 |
555 | 5 | 2015 | 80 | 50 | May 2015 | 80 |
555 | 6 | 2015 | 80 | 50 | Jun 2015 | 80 |
555 | 7 | 2015 | 80 | 50 | Jul 2015 | 80 |
555 | 8 | 2015 | 80 | 50 | Aug 2015 | 80 |
555 | 9 | 2015 | 80 | 50 | Sep 2015 | 80 |
555 | 10 | 2015 | 80 | 50 | Oct 2015 | 80 |
555 | 11 | 2015 | 80 | 50 | Nov 2015 | 80 |
555 | 12 | 2015 | 80 | 50 | Dec 2015 | 80 |
And I need:
Output:
ID,MONTH,YEAR,FINAL_FIELD1,FINAL_FIELD2
1000,10,2014,bb,-
1000,11.2014,bb,-
1000,12,2014,bb,-
1000,1,2015,bb,-
1000,2,205,bb,-
1000.3,2015,rev,bb
1000,4,2015,rev,bb
1000,5,2015,soul,rev
555,2,2015,40,-
555,3,2015,50,40
555,4,2015,80,50
100,6,2010,555
I think the problem happens when I have MONTH2 and YEAR2 < MONTH and YEAR, and I have a FIELD1 and FIELD2. I need put FIELD2(or FIELD1, it´s no problem) in the previous date.
Thank you.