topic Re: Re: Determine multiple fields from a single field based on condition in QlikView

Determine multiple fields from a single field based on condition

techvarun — Mon, 03 Nov 2014 10:37:54 GMT

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

Re: Determine multiple fields from a single field based on condition

MK_QSL — Mon, 03 Nov 2014 10:47:24 GMT

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;

Re: Determine multiple fields from a single field based on condition

its_anandrjs — Mon, 03 Nov 2014 10:57:47 GMT

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

Re: Re: Determine multiple fields from a single field based on condition

techvarun — Mon, 03 Nov 2014 11:30:38 GMT

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

Re: Re: Determine multiple fields from a single field based on condition

techvarun — Mon, 03 Nov 2014 11:32:30 GMT

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

Re: Determine multiple fields from a single field based on condition

MK_QSL — Mon, 03 Nov 2014 11:36:20 GMT

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

];

Re: Re: Determine multiple fields from a single field based on condition

its_anandrjs — Mon, 03 Nov 2014 11:48:48 GMT

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

Re: Determine multiple fields from a single field based on condition

techvarun — Mon, 03 Nov 2014 12:11:49 GMT

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

Re: Re: Determine multiple fields from a single field based on condition

its_anandrjs — Mon, 03 Nov 2014 12:20:59 GMT

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

Re: Determine multiple fields from a single field based on condition

techvarun — Tue, 04 Nov 2014 12:08:28 GMT

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?

Re: Determine multiple fields from a single field based on condition

MK_QSL — Tue, 04 Nov 2014 12:16:11 GMT

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

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

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

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