LAST DAY VALUE - or Last transaction per day- BY S... - Qlik Community

Report Inappropriate Content · ‎2010-04-29

<body>Hello friends!!!, I hope someone can give me a hand! My problem is: I have different products in one table, which change their numbers according to transactions made during the day. In one day maybe have many or any transactions, I need extract through the script the final value recorded in the day for each (x) product. I know I could handle this through the application but the file is very heavy, especially when the purpose is to make an annual statistical report for example. Graphically the situation is as follows: information in the cell yellow is what I want to call from my script and not the full list. (for each day of the month) <table border="0" width="421" cellpadding="0" cellspacing="0" style="margin: auto auto auto 4.65pt; width: 315.75pt; border-collapse: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt;" class="MsoNormalTable"> <tbody> <tr style="height: 12.75pt; mso-yfti-irow: 0;"> <td width="113" valign="bottom" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #ebebeb; padding-bottom: 0cm; width: 85pt; padding-top: 0cm; height: 12.75pt; mso-border-alt: solid gainsboro .5pt; border: gainsboro 1pt solid;"> Transaction date </td> <td width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: gainsboro 1pt solid; padding-left: 5.4pt; background: #ebebeb; padding-bottom: 0cm; border-left: #e0dfe3; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-top-alt: solid gainsboro .5pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> Transaction time </td> <td width="74" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: gainsboro 1pt solid; padding-left: 5.4pt; background: #ebebeb; padding-bottom: 0cm; border-left: #e0dfe3; width: 55.75pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-top-alt: solid gainsboro .5pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> Annual demand </td> <td width="120" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: gainsboro 1pt solid; padding-left: 5.4pt; background: #ebebeb; padding-bottom: 0cm; border-left: #e0dfe3; width: 90pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-top-alt: solid gainsboro .5pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> Total on-hand balance </td> </tr> <tr style="height: 12.75pt; mso-yfti-irow: 1;"> <td width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: yellow; padding-bottom: 0cm; border-left: gainsboro 1pt solid; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt; mso-border-left-alt: solid gainsboro .5pt;"> 20100101 </td> <td x:num="0.99983796296296301" width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: yellow; padding-bottom: 0cm; border-left: #e0dfe3; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> <st1:time minute="59" hour="23">23:59:46</st1:time> </td> <td x:num="2900144" width="74" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: yellow; padding-bottom: 0cm; border-left: #e0dfe3; width: 55.75pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 2 900 144 </td> <td x:num="130436" width="120" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: yellow; padding-bottom: 0cm; border-left: #e0dfe3; width: 90pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 130 436 </td> </tr> <tr style="height: 12.75pt; mso-yfti-irow: 2;"> <td width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: gainsboro 1pt solid; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt; mso-border-left-alt: solid gainsboro .5pt;"> 20100101 </td> <td x:num="0.99879629629629629" width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: #e0dfe3; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> <st1:time minute="58" hour="23">23:58:16</st1:time> </td> <td x:num="2900144" width="74" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: #e0dfe3; width: 55.75pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 2 900 144 </td> <td x:num="130438" width="120" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: #e0dfe3; width: 90pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 130 438 </td> </tr> <tr style="height: 12.75pt; mso-yfti-irow: 3;"> <td width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: gainsboro 1pt solid; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt; mso-border-left-alt: solid gainsboro .5pt;"> 20100101 </td> <td x:num="0.99798611111111113" width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: #e0dfe3; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> <st1:time minute="57" hour="23">23:57:06</st1:time> </td> <td x:num="2900144" width="74" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: #e0dfe3; width: 55.75pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 2 900 144 </td> <td x:num="130440" width="120" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: #e0dfe3; width: 90pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 130 440 </td> </tr> <tr style="height: 12.75pt; mso-yfti-irow: 4;"> <td width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: gainsboro 1pt solid; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt; mso-border-left-alt: solid gainsboro .5pt;"> 20100101 </td> <td x:num="0.99677083333333327" width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: #e0dfe3; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> <st1:time minute="55" hour="23">23:55:21</st1:time> </td> <td x:num="2900144" width="74" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: #e0dfe3; width: 55.75pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 2 900 144 </td> <td x:num="130442" width="120" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: #e0dfe3; width: 90pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 130 442 </td> </tr> <tr style="height: 12.75pt; mso-yfti-irow: 5;"> <td width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: gainsboro 1pt solid; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt; mso-border-left-alt: solid gainsboro .5pt;"> 20100101 </td> <td x:num="0.99659722222222225" width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: #e0dfe3; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> <st1:time minute="55" hour="23">23:55:06</st1:time> </td> <td x:num="2900144" width="74" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: #e0dfe3; width: 55.75pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 2 900 144 </td> <td x:num="130444" width="120" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: #e0dfe3; width: 90pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 130 444 </td> </tr> <tr style="height: 12.75pt; mso-yfti-irow: 6;"> <td width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: gainsboro 1pt solid; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt; mso-border-left-alt: solid gainsboro .5pt;"> 20100101 </td> <td x:num="0.99589120370370365" width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: #e0dfe3; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> <st1:time minute="54" hour="23">23:54:05</st1:time> </td> <td x:num="2900144" width="74" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: #e0dfe3; width: 55.75pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 2 900 144 </td> <td x:num="130446" width="120" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: whitesmoke; padding-bottom: 0cm; border-left: #e0dfe3; width: 90pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 130 446 </td> </tr> <tr style="height: 12.75pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes;"> <td width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: gainsboro 1pt solid; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt; mso-border-left-alt: solid gainsboro .5pt;"> 20100101 </td> <td x:num="0.99484953703703705" width="113" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: #e0dfe3; width: 85pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> <st1:time minute="52" hour="23">23:52:35</st1:time> </td> <td x:num="2900144" width="74" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: #e0dfe3; width: 55.75pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 2 900 144 </td> <td x:num="130448" width="120" valign="bottom" style="border-right: gainsboro 1pt solid; padding-right: 5.4pt; border-top: #e0dfe3; padding-left: 5.4pt; background: white; padding-bottom: 0cm; border-left: #e0dfe3; width: 90pt; padding-top: 0cm; border-bottom: gainsboro 1pt solid; height: 12.75pt; mso-border-bottom-alt: solid gainsboro .5pt; mso-border-right-alt: solid gainsboro .5pt;"> 130 448 </td> </tr> </tbody> </table> In advance.... thank you very much! Karen </body>

amars · ‎2010-04-29

Hi Karen

In script, a statement like this

Load [Product Code], [Transaction Date] , Max([Transaction Time]) as MaxTranTime
Resident SourceTable
Group by [Product Code], [Transaction Date] ;

will do the thing or else if u wish to do it in chart u can simply put Chart Dimension as [Product Code], [Transaction Date] & then use an expression like Max([Transaction Time]) to view the result in a chart.

With Regards

Amar

Report Inappropriate Content · ‎2010-04-29

Hi Amar,

Thanks for you answer, but when i test the script with : Max([Transaction Time]) as MaxTranTime , this show me one mesage like "Invalid expression".

the second alternative you propose is not very optimal for my purposes because it would load all records from my database in the file, and this is precisely what I do not want and do the optimization from the script.

Thanks again for the keedback

Karen

Report Inappropriate Content · ‎2010-04-29

Hi Amar,

Thanks for you answer, but when i test the script with : Max([Transaction Time]) as MaxTranTime , this show me one mesage like "Invalid expression".

the second alternative you propose is not very optimal for my purposes because it would load all records from my database in the file, and this is precisely what I do not want and do the optimization from the script.

Thanks again for the keedback

Karen

amars · ‎2010-04-29

Hi Karan,

The Field Names are case Sensitive so use [Transaction time] instead of [Transaction Time] . Also with the second option u can restrict the no of values to be shown by using appropriate expression i.e If(FilteringCondition , Max([Transaction time])) like that.

With Regards

Amar

Report Inappropriate Content · ‎2010-09-01

Hi guys!

I'm having the same problem as Karen. I'm getting an invalid expression when using "max" on my load script? Is there an alternative for using "max" in the load script?

Hope you can help us solve our problem..

Thanks in advance!

Lester

LAST DAY VALUE - or Last transaction per day- BY SCRIPT