Field Value lost after left join.

[mazacini]

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

[#2]

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

[nagaiank]

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 ;

[mazacini]

Hi Rahul

That doesn't seem to work.

Can you explain the idea behing your solution?

Joe

[CELAMBARASAN]

Hi,

     Table t2 have loaded before temp3?

     structure of the t2 table?

[#6]

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

R

[mazacini]

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:

[mazacini]

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

[mazacini]

Hi

My apologies, I do not understand you question?

Joe

[#10 · Accepted solution]

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.