topic what's the expression in QlikView

what's the expression

Wed, 20 Jul 2011 11:31:56 GMT

Hi,

i have this table and i need an expression to evaluate the NewDate

Date	Field1	Field2	NewDate
17/4/2011	AAA	BB	?
18/4/2011	AAAA	CM	?
19/4/2011	AAAB	CM	?
20/4/2011	AAAC	CM	?
21/4/2011	AAAD	CM	?
22/4/2011	AAAE	RH	?
23/4/2011	AAAF	CM	?
24/4/2011	AAAG	BB	?

in the load i added load for left(Field1,3) and called it Field3

what i want is that"if Field3=Field1 and Field2 is equal in both records"

for example in case of Field1=AAAG and the left(Field,3) will equal AAA the corresponding value for Field2 in this record =BB

and there's AAA in Field1 and its corresponding value in Field2 is BB

So, i want the NewDate for both records to be the minimum date of both records which is 17/4/2011

The final result i want is

Date	Field1	Field2	NewDate
17/4/2011	AAA	BB	17/4/2011
18/4/2011	AAAA	CM	18/4/2011
19/4/201	AAAB	CM	19/4/2011
20/4/2011	AAAC	CM	20/4/2011
21/4/2011	AAAD	CM	21/4/2011
22/4/2011	AAAE	RH	22/4/2011
23/4/2011	AAAF	CM	23/4/2011
24/4/2011	AAAG	BB	17/4/2011

Thanks in advance

what's the expression

pat_agen — Wed, 20 Jul 2011 11:50:26 GMT

hi,

try this:

1. add to your first load the following field

left(Field1,3) & '|' & Field2 as NewDateKey

2. add the following table

newDate:

load

NewDateKey,

min(Date2) as NewDate

resident loadData

group by NewDateKey;

3. join this table back to your initail load:

     originalData:
     join (originalData) load
           NewDateKey,
           NewDate
     resident newDate;

4. then drop the work table

drop table newDate;

what's the expression

swuehl — Wed, 20 Jul 2011 11:51:30 GMT

Hi,

I think you could also use combined field3:

left(Field1,3) & Field2 as Field3

Then load a new table using your table sorted by Field3 and Date

newDateTable:

Load *,

if(peek(Field3)=Field3,peek(NewDate), NewDate) as NewDate

resident yourTable order by Field3, Date ASC;

Just an idea,

Stefan

what's the expression

kaushiknsolanki — Wed, 20 Jul 2011 12:00:48 GMT

Try this.

Data_Temp:

load * inline [

Date1, Field11, Field21

17/4/2011, AAA, BB

18/4/2011, AAAA, CM

19/4/2011, AAAB, CM

20/4/2011, AAAC, CM

21/4/2011, AAAD, CM

22/4/2011, AAAE, RH

23/4/2011, AAAF, CM

24/4/2011, AAAG, BB

];

Data:

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

Field11 as Field1,

left(Field11,3) as Field3,

Field21 as Field2

Resident Data_Temp;

Data1:

Load

Field3 ,

Field2 ,

Date(min(Date)) as Date2

Resident Data group by Field2,Field3;

Drop table Data_Temp;

Regards,

Kaushik Solanki

what's the expression

Wed, 20 Jul 2011 12:48:28 GMT

No,didn't give me the wanted result

it compares NewDateKey field with itself but i want it to compare it with Field1

So,the NewDate wasn't right

what's the expression

Wed, 20 Jul 2011 12:59:04 GMT

Not the expected result

it gives me each time the previous date

Re: what's the expression

lironbaram — Wed, 20 Jul 2011 13:02:47 GMT

hei mona

attach is example of what you asked for

the example have contains two solutions:

1. in an expression look at the first expression

2. in the load script look inside the script

hope its helps you

what's the expression

Wed, 20 Jul 2011 13:03:55 GMT

No,in your case it will give for each AAA-CM the minimum date for them all

but what i want is that

for example when getting the left(Field1,3) in case of AAAE which is AAA and have Field2=CM, i want to compare it with the first AAA which has the Field2=BB , so both of them shouldn't have the same NewDate which is the minimum one

No,Because of Field2 is different in both records each one will reserve its Date as it's

Is the problem clear??

what's the expression

pat_agen — Wed, 20 Jul 2011 13:04:42 GMT

I must have misunderstood.

I thought you wanted to test the first three characters of field 1 plus the value of field 2. This is what the NewDateKey contains.

for which record does it produce the wrong result, and what is the reulst you woudl expect to have - this may help me understand your reuirement better.

what's the expression

Wed, 20 Jul 2011 13:14:00 GMT

Thanks for your help

but unfortunately not the wanted result

i want each time the( left(Field1,3)=Field3) to compare it self with Field1 again

so the only 2 records which will have the same date is the first and last one because

the first record has Field1=AAA and Field2=BB

record2 has Field3=AAA and Field2=CM and Field1=AAAA

record3 has Field3=AAA and Field2=CM and Field1=AAAB

when comparing record2 and 3 with record1 comparin Field3 in both with Field1 in record1 and comparing Field2 in the 3 records

record 2 not equal to record1 and the same record3 is not equal to record1

so each one will reserve each Date

I hope the problem is clear now

and Thanks

what's the expression

Wed, 20 Jul 2011 13:15:47 GMT

Never Mind,

The expected result is the second table in my original post

the only 2 records will be affected are record one and three

Re: what's the expression

lironbaram — Wed, 20 Jul 2011 13:26:47 GMT

hei mona

check the new attachment

last try

look at the load script

Re: what's the expression

Thu, 21 Jul 2011 06:29:58 GMT

Thanks Alot