Regarding isnull function in my logic

[#1]

Hello Friends,

I am stuck up with my logic.

My scenario:

I have 2 tables with common key and amount field. I am connecting these tables to a link table for some future logic.

Now in link-table,

I need to display Table2 Amount as as always. But, if table2 amount is null, then I need to display amount from table1 for any key values.

I have attached my application for your reference, kindly let me know your valuable feedback.

Kind regards,

Kiru

[albertovarela]

Hello,  It is difficult to understand what you did on the LinkTable2 Tab so I took a different approach by joining the 2 tables and then comparing the 2 amounts.  Please take a look and see if it's the result you were looking for.

Regards

[#3]

Hello Alberto,

Thanks so much and I sincerely  appreciate your time. Actually,  I need to implement this logic using link-table to achieve some other  future requirements. Kindly let me know if it could be done along with the link table? I have simplified the application and attached for your reference.

Thanks,

Kiru

[#4]

Hello Alberto,

I appreciate your time and thanks a lot.  The outer join is taking a lot of time to reload, is there any other way I can implement this same logic?

Kind regards,

Kiru

[albertovarela]

Hi Kiru, I used the outer join for the sake of the example as there was only a few rows of data on each table (see my note on the script) so depending on what you would like to accomplish you could determine which table to choose as the base for your fact table and then do a left or right join.

Hope this helps,

Alberto

[#6]

Hello Alberto,

I  honestly lost my temper and my data model  tables goes from PR to PO.

So the shared report  should contains all the default amount from PO table for any key  eg: say 1 and in case if PO amount  is not present for  key= 1 then it need to display PR.

But in PR tab, It need to display the PR amount for the key = 1.

Kindly let me know what to choose as a base to proceed?

I sincerely appreciate your valuable time.

Kind regards,

Kiru

[albertovarela]

Hi, I updated the script to build the fact table based on the PO table. It's not simple to tell if this is the right way without knowing the business process you want to analyze. Please read my notes and make any change if needed. ..  Cheers

[MarcoWedel]

Hi,

as I understand you like to keep your table structure, one possible solution could be:

Fact_PR:
LOAD * Inline [
Key, Amount
2,500
443567,429
1936,2834
];

Fact_PO:
LOAD * Inline [
Key, Amount,val
443567,429,6
1936,2971,9
];

Fact1:
LOAD
Key as Key1,
Amount as Amount1
Resident Fact_PR;

Fact2:
LOAD
Key as Key2,
Amount as Amount2
Resident Fact_PO;

Lk2:
LOAD
Key as Link_Key,
Amount as Final_Amount,
'2' as Amount_Flag
Resident Fact_PO
Where not IsNull(Amount);

LOAD
Key as Link_Key,
Amount as Final_Amount,
'1' as Amount_Flag
Resident Fact_PR
Where not (IsNull(Amount) or Exists(Link_Key,Key));

LinkTable1:
LOAD Distinct
Key1,
Key1 as Link_Key
Resident 
Fact1;

Outer Join (LinkTable1)
LOAD Distinct
Key2,
Key2 as Link_Key
Resident Fact2;

DROP Tables Fact_PR, Fact_PO;

hope this helps

regards

Marco