Determine multiple fields from a single field based on condition

[techvarun]

Hello All

         I have a table with 3 fields

Value	Week1	Week2
-21141.5	10/27/2014	10/27/2014
-19016.3	10/27/2014	10/20/2014
-16038.1	10/27/2014	10/13/2014
-15103	10/27/2014	10/6/2014
-12471.5	10/27/2014	9/29/2014
-11572	10/27/2014	10/27/2014
-11454.9	10/27/2014	10/20/2014
-10400.5	10/27/2014	10/13/2014
-10331.9	10/27/2014	10/6/2014
-10136.9	10/27/2014	9/29/2014
-9894.09	10/27/2014	10/27/2014
-9787.82	10/27/2014	10/20/2014
-9787.82	10/27/2014	10/13/2014
-9148.38	10/27/2014	10/6/2014
-8858.29	10/27/2014	10/20/2014
-8178.99	10/27/2014	10/27/2014
-7805.58	10/27/2014	10/20/2014
-7507.92	10/27/2014	10/13/2014
-7301.05	10/27/2014	10/6/2014
-6702.96	10/27/2014	9/29/2014
-6566.11	10/27/2014	10/27/2014
-5860.31	10/27/2014	10/20/2014

Now i want to determine the field value in to 5 different fields based on below condition

Field1 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 1

Field2 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 2

Field3 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 3

Field4 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 4

Field5 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 5 And my current week is 11/3/2014 but i have to maintain dynamic.

Please help me to create the script

Thanks in advance

Varun    

[MK_QSL]

Load

  *,

  IF(Date(Week1) = Date(Today()-1) and Date(Week2) = Date(Today()-1),'Field1',

  IF(Date(Week1) = Date(Today()-1) and Date(Week2) = Date(Today()-2),'Field2',

  IF(Date(Week1) = Date(Today()-1) and Date(Week2) = Date(Today()-3),'Field3',

  IF(Date(Week1) = Date(Today()-1) and Date(Week2) = Date(Today()-4),'Field4',

  IF(Date(Week1) = Date(Today()-1) and Date(Week2) = Date(Today()-5),'Field5'))))) as Field;

Load

  Value,

  Date(Date#(Week1,'MM/DD/YYYY')) as Week1,

  Date(Date#(Week2,'MM/DD/YYYY')) as Week2

From TableName;

[its_anandrjs]

Hi,

Try to load table like this way

Raw:

LOAD Value,

Date(Date#(Week1,'MM/DD/YYYY'),'MM/DD/YYYY') AS Week1,

Date(Date#(Week2,'MM/DD/YYYY'),'MM/DD/YYYY') AS Week2;

LOAD * Inline

[

Value, Week1, Week2

-21141.5, 10/27/2014, 10/27/2014

-19016.3, 10/27/2014, 10/20/2014

-16038.1, 10/27/2014, 10/13/2014

-15103, 10/27/2014, 10/6/2014

-12471.5, 10/27/2014, 9/29/2014

-11572, 10/27/2014, 10/27/2014

-11454.9, 10/27/2014, 10/20/2014

-10400.5, 10/27/2014, 10/13/2014

-10331.9, 10/27/2014, 10/6/2014

-10136.9, 10/27/2014, 9/29/2014

-9894.09, 10/27/2014, 10/27/2014

-9787.82, 10/27/2014, 10/20/2014

-9787.82, 10/27/2014, 10/13/2014

-9148.38, 10/27/2014, 10/6/2014

-8858.29, 10/27/2014, 10/20/2014

-8178.99, 10/27/2014, 10/27/2014

-7805.58, 10/27/2014, 10/20/2014

-7507.92, 10/27/2014, 10/13/2014

-7301.05, 10/27/2014 ,10/6/2014

-6702.96, 10/27/2014, 9/29/2014

-6566.11, 10/27/2014, 10/27/2014

-5860.31, 10/27/2014, 10/20/2014

];

Final:

LOAD

Value, Week1, Week2,

if( (Week1 = Date( WeekStart(  Today() )-1) and Week2 = Date( WeekStart(  Today() )-1) ),'Field1',

if( (Week1 = Date( WeekStart(  Today() )-1) and Week2 = Date( WeekStart(  Today() )-2) ),'Field2',

if( (Week1 = Date( WeekStart(  Today() )-1) and Week2 = Date( WeekStart(  Today() )-3) ),'Field3',

if( (Week1 = Date( WeekStart(  Today() )-1) and Week2 = Date( WeekStart(  Today() )-4) ),'Field4',

if( (Week1 = Date( WeekStart(  Today() )-1) and Week2 = Date( WeekStart(  Today() )-5) ),'Field5'

))))) as NewFlag

Resident Raw;

DROP Table Raw;

But the data you provide it is for previous month check this in current month data in your system

Regards

Anand

[techvarun]

Hi Anand and Manish,

Many Thanks for ur super fast reply.

  I need the result like this

Field 1

-21141.5

-11572

-9894.09

-8178.99

-6566.11

Field1 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 1

Field 2

-19016.3

-11454.9

-9787.82

-8858.29

-7805.58

-5860.31

Field2 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 2

Field3

-16038.1

-10400.5

-9787.82

-7507.92

Field3 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 3

Field4

-15103

-10331.9

-9148.38

-7301.05

Field4 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 4

Field 5

-12471.5

-10136.9

-6702.96

Field5 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 5

Thanks

Varun

[techvarun]

The resultant fields

Field 1

Field 2

Field 3

Field 4

Field 5

Should be the Field Value based on the above conditions

Thanks

Varun

[MK_QSL]

Load

  *,

  If(Week1 = Week(Today())-1 and Week2 = Week(Today())-1, 'Field1',

  If(Week1 = Week(Today())-1 and Week2 = Week(Today())-2, 'Field2',

  If(Week1 = Week(Today())-1 and Week2 = Week(Today())-3, 'Field3',

  If(Week1 = Week(Today())-1 and Week2 = Week(Today())-4, 'Field4',

  If(Week1 = Week(Today())-1 and Week2 = Week(Today())-4, 'Field4', 'Field5'))))) as Field;

Load

  Value,

  Date(Date#(Week1,'MM/DD/YYYY')) as Date1,

  Week(Date(Date#(Week1,'MM/DD/YYYY'))) as Week1,

  Date(Date#(Week2,'MM/DD/YYYY')) as Date2,

  Week(Date(Date#(Week2,'MM/DD/YYYY'))) as Week2

Inline

[

  Value, Week1, Week2

  -21141.5, 10/27/2014, 10/27/2014

  -19016.3, 10/27/2014, 10/20/2014

  -16038.1, 10/27/2014, 10/13/2014

  -15103,   10/27/2014, 10/6/2014

  -12471.5, 10/27/2014, 9/29/2014

  -11572,   10/27/2014, 10/27/2014

  -11454.9, 10/27/2014, 10/20/2014

  -10400.5, 10/27/2014, 10/13/2014

  -10331.9, 10/27/2014, 10/6/2014

  -10136.9, 10/27/2014, 9/29/2014

  -9894.09, 10/27/2014, 10/27/2014

  -9787.82, 10/27/2014, 10/20/2014

  -9787.82, 10/27/2014, 10/13/2014

  -9148.38, 10/27/2014, 10/6/2014

  -8858.29, 10/27/2014, 10/20/2014

  -8178.99, 10/27/2014, 10/27/2014

  -7805.58, 10/27/2014, 10/20/2014

  -7507.92, 10/27/2014, 10/13/2014

  -7301.05, 10/27/2014 ,10/6/2014

  -6702.96, 10/27/2014, 9/29/2014

  -6566.11, 10/27/2014, 10/27/2014

  -5860.31, 10/27/2014, 10/20/2014

];

[its_anandrjs]

Hi,

Ok then in place of  'Field1' use Value field

Final:

LOAD

Value, Week1, Week2,

if( (Week1 = Date( WeekStart(  Today() )-1) and Week2 = Date( WeekStart(  Today() )-1) ),Value,

if( (Week1 = Date( WeekStart(  Today() )-1) and Week2 = Date( WeekStart(  Today() )-2) ),Value,

if( (Week1 = Date( WeekStart(  Today() )-1) and Week2 = Date( WeekStart(  Today() )-3) ),Value,

if( (Week1 = Date( WeekStart(  Today() )-1) and Week2 = Date( WeekStart(  Today() )-4) ),Value,

if( (Week1 = Date( WeekStart(  Today() )-1) and Week2 = Date( WeekStart(  Today() )-5) ),Value

))))) as NewFlag

Resident Raw;

Also Can you fill this formulas with Current Week values for all -1,-2,-3,-4, and -5

Field1 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 1

Field2 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 2

Field3 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 3

Field4 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 4

Field5 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 5

Regards

Anand

[techvarun]

I need a result table like this

Field 1	Field 2	Field3	Field4	Field 5
-21141.5	-19016.3	-16038.1	-15103	-12471.5
-11572	-11454.9	-10400.5	-10331.9	-10136.9
-9894.09	-9787.82	-9787.82	-9148.38	-6702.96
-8178.99	-8858.29	-7507.92	-7301.05
-6566.11	-7805.58
	-5860.31

I want to use field1.....Field 5 in some computations

[its_anandrjs · Accepted solution]

Try to load like

Raw:

LOAD Value,

Date(Date#(Week1,'MM/DD/YYYY'),'MM/DD/YYYY') AS Week1Date,

Date(Date#(Week2,'MM/DD/YYYY'),'MM/DD/YYYY') AS Week2Date,

Week(Date(Date#(Week1,'MM/DD/YYYY'),'MM/DD/YYYY')) AS Week1,

Week(Date(Date#(Week2,'MM/DD/YYYY'),'MM/DD/YYYY')) AS Week2;

LOAD * Inline

[

Value, Week1, Week2

-21141.5, 10/27/2014, 10/27/2014

-19016.3, 10/27/2014, 10/20/2014

-16038.1, 10/27/2014, 10/13/2014

-15103, 10/27/2014, 10/6/2014

-12471.5, 10/27/2014, 9/29/2014

-11572, 10/27/2014, 10/27/2014

-11454.9, 10/27/2014, 10/20/2014

-10400.5, 10/27/2014, 10/13/2014

-10331.9, 10/27/2014, 10/6/2014

-10136.9, 10/27/2014, 9/29/2014

-9894.09, 10/27/2014, 10/27/2014

-9787.82, 10/27/2014, 10/20/2014

-9787.82, 10/27/2014, 10/13/2014

-9148.38, 10/27/2014, 10/6/2014

-8858.29, 10/27/2014, 10/20/2014

-8178.99, 10/27/2014, 10/27/2014

-7805.58, 10/27/2014, 10/20/2014

-7507.92, 10/27/2014, 10/13/2014

-7301.05, 10/27/2014 ,10/6/2014

-6702.96, 11/02/2014, 11/30/2014

-6566.11, 11/02/2014, 11/30/2014

-5860.31, 11/02/2014, 11/30/2014

];

Final:

LOAD

Value, Week1, Week2, Week1Date, Week2Date,

if( (Week1 = Week(Date( (  Today() )-1)) and Week2 = Week(Date( (  Today() )))-1 ),Value) as 'Field1',

if( (Week1 = week(Date( (  Today() )-1)) and Week2 = Week(Date( (  Today() )))-2 ),Value) as 'Field2',

if( (Week1 = week(Date( (  Today() )-1)) and Week2 = Week(Date( (  Today() )))-3 ),Value) as 'Field3',

if( (Week1 = week(Date( (  Today() )-1)) and Week2 = Week(Date( (  Today() )))-4 ),Value) as 'Field4',

if( (Week1 = week(Date( (  Today() )-1)) and Week2 = Week(Date( (  Today() )))-5 ),Value) as 'Field5'

Resident Raw;

DROP Table Raw;

Regards

Anand

[techvarun]

Hi Anand and Manish

    What if my week1 and Week2 is in yyyymm format?

        201444  44th month of 2014

         201455 55th month of 2015 etc

where to edit the above script?