Implement the dynamic script logic to get the order details.

[mcaahmad93]

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

[Or · Accepted solution]

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;

[mcaahmad93]

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

[Or]

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.

[iahmadmca]

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

[Or]

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.

[iahmadmca]

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: