topic Regarding isnull function in my logic in QlikView

Regarding isnull function in my logic

Sat, 15 Nov 2014 00:19:21 GMT

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

Re: Regarding isnull function in my logic

albertovarela — Sat, 15 Nov 2014 01:42:46 GMT

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

Re: Regarding isnull function in my logic

Sat, 15 Nov 2014 05:17:20 GMT

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

Re: Regarding isnull function in my logic

Mon, 17 Nov 2014 17:45:07 GMT

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

Re: Regarding isnull function in my logic

albertovarela — Mon, 17 Nov 2014 18:26:15 GMT

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

Re: Regarding isnull function in my logic

Mon, 17 Nov 2014 18:45:11 GMT

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

Re: Regarding isnull function in my logic

albertovarela — Mon, 17 Nov 2014 21:41:58 GMT

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

Re: Regarding isnull function in my logic

MarcoWedel — Mon, 17 Nov 2014 23:49:25 GMT

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