topic Re: Re: Re: Re: Need Help.. in QlikView

Need Help..

Mon, 06 Jan 2014 15:47:17 GMT

Hi Community,

Kindly help me in solving the below issue.

Table1:

Material No	Plant	Qty
28112	0001	500
1234	0002	100

Table2:

Order Date	KEY No	Plant	Qty_1
2-11-2013	28112	0001	300
22-11-2013	28112	0001	200
24-11-2013	28112	0001	300
05-12-2013	1234	0002	80
06-12-2013	1234	0002	30

From the above data, I need to check that for Particular Plant and Material, how many orders are placed as per order date.

Thanks in Advance.............

Re: Need Help..

Gysbert_Wassenaar — Mon, 06 Jan 2014 16:13:44 GMT

See attached qvw

Re: Need Help..

Nicole-Smith — Mon, 06 Jan 2014 16:16:28 GMT

Mine is slightly different from Gysbert Wassenaar's. You can choose which one works better for you:

Table1:

LOAD *, [Material No] & '|' & [Plant] as Key INLINE [

Material No, Plant, ATP Qty

28112, A016, 500

1234, A017, 100

];

Table2:

LOAD *, [Material No] & '|' & [Plant] as Key INLINE [

Order Date, Material No, Plant, Ordered Qty

2-11-2013, 28112, A016, 300

22-11-2013, 28112, A016, 200

24-11-2013, 28112, A016, 300

05-12-2013, 1234, A017, 80

06-12-2013, 1234, A017, 30

];

Output:

LOAD date#([Order Date], 'D-MM-YYYY') as [Order Date],

    [Material No],

    [Plant],

    if([Plant] = Previous([Plant]) and [Material No]=Previous([Material No]), PEEK([Cumulative Qty]), Lookup('ATP Qty', 'Key', Key, 'Table1')) as [ATP Qty],

    [Ordered Qty],

    if([Plant] = Previous([Plant]) and [Material No]=Previous([Material No]), PEEK([Cumulative Qty]), Lookup('ATP Qty', 'Key', Key, 'Table1'))-[Ordered Qty] as [Cumulative Qty],

    if(if([Plant] = Previous([Plant]) and [Material No]=Previous([Material No]), PEEK([Cumulative Qty]), Lookup('ATP Qty', 'Key', Key, 'Table1'))-[Ordered Qty]<0, 'Invalid', 'OK') as [Status]

RESIDENT Table2

ORDER BY [Plant], [Material No], [Order Date];

DROP TABLES Table1, Table2;

Re: Need Help..

Mon, 06 Jan 2014 16:48:08 GMT

Thanks Gysbert and Nicole

It works Seamlessly...........

Re: Need Help..

Nicole-Smith — Tue, 07 Jan 2014 14:16:27 GMT

What would the original data look like for that? Because I'm not seeing how you would get that from the data in your original post...

Re: Re: Need Help..

Tue, 07 Jan 2014 14:27:29 GMT

HI Nicole,

PFA,

Re: Re: Re: Need Help..

Nicole-Smith — Tue, 07 Jan 2014 14:45:35 GMT

How about the attached?

Re: Re: Re: Re: Need Help..

Thu, 09 Jan 2014 14:08:45 GMT

Hi Nicole,

One more problem..

PFA,...

Re: Re: Re: Re: Need Help..

Thu, 09 Jan 2014 15:16:37 GMT

Hi Community,

anyone can help me..............

Re: Re: Re: Re: Re: Need Help..

Nicole-Smith — Thu, 09 Jan 2014 18:27:37 GMT

Is this what you mean?

Re: Need Help..

Nicole-Smith — Thu, 09 Jan 2014 18:56:18 GMT

Just noticed that someone already answered this here: Challenging.............

Please do not post the same thing multiple times, or if you do, at least post a link to the new post on the old one...

Re: Need Help..

Tue, 14 Jan 2014 02:02:13 GMT

Hi Nicole,

I Need Help....

Please see this link..

http://community.qlik.com/thread/104095