topic Re: Re: Regarding logic for my scenario in QlikView

Regarding logic for my scenario

Fri, 14 Nov 2014 21:08:20 GMT

Hello Friends,

I appreciate your time.

I am not sure about the logic for my scenario, kindly share your ideas.

I have 2 tables, say

Table1:

Key,

Amount

Table2:

Key,

Amount

Now I have to show the Amount from Table 2 as default in all reports, but in case if there is no amount from Table 2, then I have to show the Amount from Table 1.

Kindly let me know your ideas.

Kind regards,

Kiru

Re: Regarding logic for my scenario

Fri, 14 Nov 2014 21:28:30 GMT

Look at the JOIN statement and consider bringing those tables together based on your KEY field. But if you want to keep them separate look at the ISNULL() function and use an IF for your expression where you do just what you said.

Re: Regarding logic for my scenario

Fri, 14 Nov 2014 21:39:59 GMT

Load Table1 as Mapping Table

and in Table 2 If(Isnull(Amount)=-1, ApplyMap())

Table1:

Key,

1 as Amount_Flag,

Amount

Concatenate

Table2:

Key,

2 as Amount_Flag

Amount

In Expressions or Resident If(Amount_Flag=2 and Isnull(Amount)=-1,If(Amount_Flag=1,Amount,0),0)

Re: Regarding logic for my scenario

MarcoWedel — Fri, 14 Nov 2014 21:42:38 GMT

Hi,

one solution could be:

hope this helps

regards

Marco

Re: Regarding logic for my scenario

Fri, 14 Nov 2014 21:50:37 GMT

Hello Dalton,

I appreciate your time. It is just a part of my logic and I need to consider my other tables too, I need to do my calculation in the link table which contains both the tables.

I have attached the application for your reference, kindly let me know your suggestions.

Kind regards,

Kiru

Re: Regarding logic for my scenario

Fri, 14 Nov 2014 22:10:05 GMT

Hello Eddie,

Thanks for your time. I have attached my sample script, If possible could you please implement your logic.

I sincerely appreciate your time.

Kind regards,

Kiru

Re: Regarding logic for my scenario

Fri, 14 Nov 2014 22:32:00 GMT

Hello Eddie,

I got partial output. But when Amount in Table 2 is null, it is not showing the amount from Table 1.

I have attached the application, kindly let me know your valuable suggestions.

Kind regards,

Kiru

Re: Re: Regarding logic for my scenario

Sat, 15 Nov 2014 09:15:21 GMT

Try this.. See if it works.

Re: Re: Regarding logic for my scenario

Mon, 17 Nov 2014 17:41:39 GMT

Hello Eddie,

Thanks and I appreciate your time. The only problem with the above script is it is showing both the amount(PO, PR) for the key :1936. But I have to display only PO in this case which is 2971 ( The rule is if PO is present then I have to show only PO and if not then PR)

Kind regards,

Kiru

Re: Regarding logic for my scenario

marcus_malinow — Mon, 17 Nov 2014 17:48:46 GMT

If you don't want the extra values loaded into your data you could use an Exists clause in your data load.

Table

LOAD

Key,

Amount

FROM Table1;

CONCATENATE (Table)

Load Key,

Amount

FROM Table2

WHERE Not Exists (Key);

Re: Regarding logic for my scenario

Mon, 17 Nov 2014 18:15:18 GMT

Hello Marcus,

I appreciate your time. I tried that earlier but not getting my relevant data.

My scenario for your reference.

I have 2 tables (PR, PO) with common key and amount. I always need to display only PO amount and in case if PO is not present then I need to show PR. I have attached a sample for your reference, kindly let me know. If possible, I need to implement this logic via a link table to satisfy some other future constraints as well.

Kind regards,

Kiru

Re: Regarding logic for my scenario

Mon, 17 Nov 2014 18:31:01 GMT

hi try this,

Table

LOAD

Key,

Amount as amount1

FROM Table1;

CONCATENATE (Table)

Load Key,

Amount as amount2

if(amount=amount2,amount2,amount1)

FROM Table2

Re: Regarding logic for my scenario

Mon, 17 Nov 2014 18:41:05 GMT

Hi,

I don't have access to your app, but just try, if you can use "if(len(PO)=0,PR,PO)".

Re: Regarding logic for my scenario

Mon, 17 Nov 2014 19:17:36 GMT

Hi,

Do we need to declare amount2 initially?

Re: Regarding logic for my scenario

MarcoWedel — Mon, 17 Nov 2014 22:06:16 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