topic Re: "For each" in QlikView

"For each"

ThomasMauconduit — Tue, 24 Mar 2020 11:22:18 GMT

Hello,

I need your help because i'm facing an issue that looks easy, but i can't find the answer..

I have 2 Excel documents, the first one contains the Order number and the quantity ordered, and the other contains the same Order number, and the quantity delivered.

I want to calculate for each order number : sum(quantity delivered)/sum(quantity ordered).

My problem is that i Don't know how to make QlikView understand "For each order number".

I hope i'm clear enough, if that's not the case, i would precise with pleasure !

Thank you in advance for answering.

Thomas.

Re: "For each"

venkat1 — Tue, 24 Mar 2020 11:30:33 GMT

Qlik will automatically make a relation between two different sources if it is having similar field name,if not,you can link by using renaming fields or joining concatenation methods.

Re: "For each"

ThomasMauconduit — Tue, 24 Mar 2020 11:45:37 GMT

Yes this works well, QlikView makes the relation. But still, it doesn't give me the precision "for each order number".

With this relation, i can calculate : sum of all order quantity/sum of all delivered quantity.

But this is not what i want, because, after that, i want to obtain the proportion of "order quantity that are equal to the delivered quantity".

For example i thought about this :

"For each" [Order number], if(sum([ordered quantity])=sum([delivered quantity]),1,0) as [test]

count([test]=1)/count([test]=0)

This would give me the pourcentage of Order quantity that are equal to the delivered quantity, but i need to know how to write "for each".

I hope it's clearer this time.

Thank you in advance for any answer,

Thomas.

Re: "For each"

Brett_Bleess — Wed, 15 Apr 2020 15:50:03 GMT

Not sure if the following Design Blog will help or not:

https://community.qlik.com/t5/Qlik-Design-Blog/Accumulations/ba-p/1466581

If you want to search further in this area, use the following link, there are over 700 posts and most are how-to related:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett