topic Re: How to group records in QlikView https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142400#M630633 <HTML><HEAD></HEAD><BODY><P>Option 1: Using Generic load: (<A href="https://community.qlik.com/qlik-blogpost/3629">The Generic Load</A>)</P><P></P><P><STRONG>Table:</STRONG></P><P><STRONG>Generic</STRONG></P><P><STRONG>LOAD * Inline [</STRONG></P><P><STRONG>Order | State&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Date</STRONG></P><P><STRONG>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | To be Receive&nbsp; | 01/08/2016</STRONG></P><P><STRONG>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Received&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 04/08/2016</STRONG></P><P><STRONG>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Closed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 05/08/2016</STRONG></P><P><STRONG>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | To be Receive&nbsp; | 07/07/2016</STRONG></P><P><STRONG>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Received&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 08/07/2016</STRONG></P><P><STRONG>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Closed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 09/07/2016</STRONG></P><P><STRONG>] (delimiter is |);</STRONG></P><P><STRONG><BR /></STRONG></P><P><STRONG><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/133834_Capture.PNG" style="height: auto;" /><BR /></STRONG></P></BODY></HTML> Sat, 06 Aug 2016 10:43:14 GMT sunny_talwar 2016-08-06T10:43:14Z How to group records https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142399#M630632 <HTML><HEAD></HEAD><BODY><P>Hi all</P><P></P><P>I have a table like this:</P><P></P><P>Order | State&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Date</P><P>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | To be Receive&nbsp; | 01/08/2016</P><P><SPAN style="font-size: 13.3333px;">A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Received&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 04/08/2016</SPAN></P><P><SPAN style="font-size: 13.3333px;">A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Closed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 05/08/2016</SPAN></P><P style="font-size: 13.3333px;">B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | To be Receive&nbsp; | 07/07/2016</P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Received&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 08/07/2016</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Closed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 09/07/2016</SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">I would to be able to obtain something like this:</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P style="font-size: 13.3333px;">Order | To be Received | Received&nbsp;&nbsp;&nbsp;&nbsp; | Closed</P><P style="font-size: 13.3333px;">A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | <SPAN style="font-size: 13.3333px;">01/08/2016</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | <SPAN style="font-size: 13.3333px;"> 04/08/2016 | <SPAN style="font-size: 13.3333px;">05/08/2016</SPAN></SPAN></P><P style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;"><SPAN style="font-size: 13.3333px;">A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | </SPAN><SPAN style="font-size: 13.3333px;">07/07/2016</SPAN><SPAN style="font-size: 13.3333px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | </SPAN><SPAN style="font-size: 13.3333px;"> 08/07/2016 | <SPAN style="font-size: 13.3333px;">09/07/2016</SPAN></SPAN></SPAN></P><P style="font-size: 13.3333px;"></P><P style="font-size: 13.3333px;">Any ideas?</P><P style="font-size: 13.3333px;"></P><P style="font-size: 13.3333px;">Thank in advance</P><P style="font-size: 13.3333px;">Michele</P></BODY></HTML> Sat, 06 Aug 2016 09:07:57 GMT https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142399#M630632 2016-08-06T09:07:57Z Re: How to group records https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142400#M630633 <HTML><HEAD></HEAD><BODY><P>Option 1: Using Generic load: (<A href="https://community.qlik.com/qlik-blogpost/3629">The Generic Load</A>)</P><P></P><P><STRONG>Table:</STRONG></P><P><STRONG>Generic</STRONG></P><P><STRONG>LOAD * Inline [</STRONG></P><P><STRONG>Order | State&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Date</STRONG></P><P><STRONG>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | To be Receive&nbsp; | 01/08/2016</STRONG></P><P><STRONG>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Received&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 04/08/2016</STRONG></P><P><STRONG>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Closed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 05/08/2016</STRONG></P><P><STRONG>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | To be Receive&nbsp; | 07/07/2016</STRONG></P><P><STRONG>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Received&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 08/07/2016</STRONG></P><P><STRONG>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Closed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 09/07/2016</STRONG></P><P><STRONG>] (delimiter is |);</STRONG></P><P><STRONG><BR /></STRONG></P><P><STRONG><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/133834_Capture.PNG" style="height: auto;" /><BR /></STRONG></P></BODY></HTML> Sat, 06 Aug 2016 10:43:14 GMT https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142400#M630633 sunny_talwar 2016-08-06T10:43:14Z Re: How to group records https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142401#M630634 <HTML><HEAD></HEAD><BODY><P>Option 2: Using Joins</P><P></P><P><STRONG>Table:</STRONG></P><P><STRONG>LOAD * Inline [</STRONG></P><P><STRONG>Order | State&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Date</STRONG></P><P><STRONG>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | To be Receive&nbsp; | 01/08/2016</STRONG></P><P><STRONG>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Received&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 04/08/2016</STRONG></P><P><STRONG>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Closed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 05/08/2016</STRONG></P><P><STRONG>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | To be Receive&nbsp; | 07/07/2016</STRONG></P><P><STRONG>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Received&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 08/07/2016</STRONG></P><P><STRONG>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Closed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 09/07/2016</STRONG></P><P><STRONG>] (delimiter is |);</STRONG></P><P></P><P><STRONG>FinalTable:</STRONG></P><P><STRONG>LOAD Order,</STRONG></P><P><STRONG>&nbsp; Date as [To be Receive]</STRONG></P><P><STRONG>Resident Table</STRONG></P><P><STRONG>Where State = 'To be Receive';</STRONG></P><P></P><P><STRONG>Join (FinalTable)</STRONG></P><P><STRONG>LOAD Order,</STRONG></P><P><STRONG>&nbsp; Date as [Received]</STRONG></P><P><STRONG>Resident Table</STRONG></P><P><STRONG>Where State = 'Received';</STRONG></P><P></P><P><STRONG>Join (FinalTable)</STRONG></P><P><STRONG>LOAD Order,</STRONG></P><P><STRONG>&nbsp; Date as [Closed]</STRONG></P><P><STRONG>Resident Table</STRONG></P><P><STRONG>Where State = 'Closed';</STRONG></P><P></P><P><STRONG>Drop Table Table;</STRONG></P></BODY></HTML> Sat, 06 Aug 2016 10:45:56 GMT https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142401#M630634 sunny_talwar 2016-08-06T10:45:56Z Re: How to group records https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142402#M630635 <HTML><HEAD></HEAD><BODY><P>Create Pivot table</P><P></P><P>Dimension:</P><P>Order,</P><P>State</P><P></P><P>Expression:</P><P>max(Date)</P><P></P><P>Drag and drop state field horizontally over expression</P><P></P><P><IMG alt="" class="jive-image image-1" style="max-width: 1200px; max-height: 900px;" /><IMG alt="" class="jive-image image-2" src="https://community.qlik.com/legacyfs/online/133839_pastedImage_0.png" style="max-width: 1200px; max-height: 900px;" /></P></BODY></HTML> Sat, 06 Aug 2016 11:12:58 GMT https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142402#M630635 Kushal_Chawda 2016-08-06T11:12:58Z Re: How to group records https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142403#M630636 <HTML><HEAD></HEAD><BODY><P>Kushal, If we take Date Directly output will come. Can i know the reason why are taking <STRONG>Max(Date)</STRONG></P></BODY></HTML> Sat, 06 Aug 2016 11:54:50 GMT https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142403#M630636 Anil_Babu_Samineni 2016-08-06T11:54:50Z Re: How to group records https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142404#M630637 <HTML><HEAD></HEAD><BODY><P>Yes... with pivot table is simple to obtain what i need... but with pivot table i can't sort by expression and my users must to be able to do that.</P></BODY></HTML> Sat, 06 Aug 2016 12:13:04 GMT https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142404#M630637 2016-08-06T12:13:04Z Re: How to group records https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142405#M630638 <HTML><HEAD></HEAD><BODY><P>What is the sorting requirement? I don't understand why won't you be able to sort it? Can you provide the expected output with relation to the sorting?</P></BODY></HTML> Sat, 06 Aug 2016 16:03:57 GMT https://community.qlik.com/t5/QlikView/How-to-group-records/m-p/1142405#M630638 sunny_talwar 2016-08-06T16:03:57Z