topic Re: Implement the dynamic script logic to get the order details. in QlikView

Implement the dynamic script logic to get the order details.

mcaahmad93 — Sat, 16 Nov 2024 02:42:55 GMT

Hi Team,

I have one requirement that is a little bit tricky. I want you guys to help me to do this.

Provided the below data source details. In the data source if order date is null then quantity should be changed time to time. If order date filled by a user while entering the order details then order id and quantity should be only first records it should not change. One order id should be one record only.

Sample Data source:

Sample Output :

Note: I tried this but its not working dynamically. I would like to request please help me to do this.

Regards,

Irshad Ansari

Re: Implement the dynamic script logic to get the order details.

Or — Sun, 18 Aug 2019 11:15:34 GMT

You should be able to get this done by using FirstSortedValue(). I'm not sure what your exact target output is, but you can probably figure out how to modify this pretty easily if you need to. Order dates were replaced with numeric dates to make it easier for me to finish this quickly...

Data:
Load * Inline [
Order id, Quantity, Order_Date
1, 2,
1, 3,
1, 4, 20190601
1, 6, 20190701
2, 7, 20190801
3, 10,
4, 10, 20190701 ];

Data2:
NOCONCATENATE Load [Order id],
FirstSortedValue(Quantity,alt(Order_Date,99999999)) as Quantity, /* If there are no order dates, we want the last value even though it's null, so we replace nulls with a value that's always higher than our dates. If this were regular dates, we could use Today()+9999 instead */
FirstSortedValue(Order_Date, Order_Date) as Order_Date
Resident Data
Group by [Order id];

Drop table Data;

Re: Implement the dynamic script logic to get the order details.

mcaahmad93 — Sun, 18 Aug 2019 12:22:55 GMT

Thank you very much for your valuable time.

Almost, Your logic has covered my requirement. But I have excel data source and if i am changing the quantity its getting change for both filled and non filed date.

Note: The filled order date, quantity should not change if any change happening in the data source.

Data source:

Output:

Regards,

Irshad Ansari

Re: Implement the dynamic script logic to get the order details.

Or — Sun, 18 Aug 2019 12:59:17 GMT

There is no way for us to know that you changed the value, unless you create your own historical table. Qlik will only see the values currently existing in your data.

If you want to write something that remembers the previous value, and that value isn't in your data, you'll have to save the data in Qlik (to a QVD) and compare the new data to that each time. I highly recommend not using this approach - data should be stored in the source system, not in Qlik.

Re: Implement the dynamic script logic to get the order details.

iahmadmca — Sun, 18 Aug 2019 13:27:54 GMT

Thank you for suggestion.

I think you have understood my actual requirement. Here, I have attached the updated 'QVD' and source data. If you will help me to do by this way, then it will be very helpful for me. My request to you please help me do this.

Regards

Re: Implement the dynamic script logic to get the order details.

Or — Sun, 18 Aug 2019 13:40:14 GMT

I don't think this request is feasible. The value could have changed 50 times in the first day, before you ever loaded data into QlikView, so you'll never know what the original value was. There is literally no way for us to know, based on the data available, what the original value was.

Re: Implement the dynamic script logic to get the order details.

iahmadmca — Sun, 18 Aug 2019 14:12:26 GMT

Thank you.

I am good to go with your suggestion and script.

just now, I added the one more order id 3 and quantity 12 with null order date. It is coming null but it should come 12 quantity. Is it possible? it yes then i can close this loop.

output: