topic Re: Field Value lost after left join. in QlikView

Field Value lost after left join.

mazacini — Mon, 23 Jul 2012 13:13:19 GMT

I have 2 tables as described below.

Now I want to add a calculated field ("IncVal"), which I do by creating a temporary table containing the field, then doing a join before I drop the temporary table.

If I look at the data before I drop the temporary table, I can see that the IncVal in computing correctly.

But when I look after I drop the table, there is no value for field IncVal.

Any ideas?

temp3:
load Order_Num,
     InvDate,
     InvVal,
     Flag1,
     Flag2,
     If(Flag2='A',InvVal*(MonthEnd(InvDate)-InvDate)/Day(MonthEnd(InvDate)),

If(Flag1='X',InvVal*(InvDateMonthStart(InvDate))/Day(MonthEnd(InvDate)),

InvVal)) as IncVal
resident t2 ;

left join (t2) load * resident temp3;

drop table temp3

Re: Field Value lost after left join.

Mon, 23 Jul 2012 13:21:59 GMT

Hi,

Add a dummy field In your last load statement like following:

Load *,

'Temp' as Temp

Resident temp3;

This should do the trick.

Regards

Rahul

Re: Field Value lost after left join.

nagaiank — Mon, 23 Jul 2012 13:39:47 GMT

Though I do not know the business logic in your calculation, you may try the following script and see if you get what you need:

t2:
LOAD *,If(Flag2='A',InvVal*(MonthEnd(InvDate)-InvDate)/Day(MonthEnd(InvDate)),
If(Flag1='X',InvVal*(MonthStart(InvDate))/Day(MonthEnd(InvDate)),
InvVal)) as IncVal;
load Order_Num, 
     InvDate,
     InvVal,
     Flag1,
     Flag2
    resident t2 ;

Re: Field Value lost after left join.

mazacini — Mon, 23 Jul 2012 13:48:39 GMT

Hi Rahul

That doesn't seem to work.

Can you explain the idea behing your solution?

Joe

Re: Field Value lost after left join.

CELAMBARASAN — Mon, 23 Jul 2012 14:11:36 GMT

Hi,

Table t2 have loaded before temp3?

structure of the t2 table?

Re: Field Value lost after left join.

Mon, 23 Jul 2012 14:13:05 GMT

Since you are reloading the entier temp3 table again without any modifications, QV may have recognized it as a duplicate table and thus ignored it. In my oppinion if that was the probblem adding the temp filed should have solved it.

Regards

Re: Field Value lost after left join.

mazacini — Mon, 23 Jul 2012 14:34:01 GMT

I tried that, and now I have IncVal for each Order_Num line, but also a null IncVal for each Order_Num line.

My table structire is also as follows:

Re: Field Value lost after left join.

mazacini — Mon, 23 Jul 2012 14:45:31 GMT

Hi Rahul

I think your additional explanation may have helped me resolve the matter.

I reduced the amount of fields to be joined - and it seems to have worked.

Here is my new script:

left join (t2) load Order_Num,
InvDate,
IncValresident temp3;
drop table temp3

Does that make sense? Do you know why this works?

Joe

Re: Field Value lost after left join.

mazacini — Mon, 23 Jul 2012 14:46:03 GMT

My apologies, I do not understand you question?

Joe

Re: Field Value lost after left join.

Mon, 23 Jul 2012 14:57:56 GMT

I still believe the problem was with QlikView proactively thinking that it has the table you want it to load again. Therefore it decided it (QV) would not load it. By taking of the fields from the table you have made it realize that this is a diffrent table.