topic Sorting Help in QlikView

Sorting Help

stuwannop — Fri, 14 Jun 2013 10:25:59 GMT

Hi Everyone - hope someone can help this is driving me up the wall

All I want to do is sort the contents of a QVD I load into my dashboard by date (in my context it will be createdon). First I tried to use the ORDER BY statement at the end of my load statement but I understand it doesn't work unless you are using resident. So I tried this:

CR:

LOAD

crid

createdon

amount

FROM

QVD/campaingresponses.qvd

CampaignResponse:

LOAD

Resident CR

ORDER BY createdon asc;

DROP TABLE CR;

I was hoping that would leave me with a table called CampaignResponse with the data nicely sorted. Not so apparently I can't see any table.

Incidentally what I am trying to achieve is simply a chartshowing the number of "campaign responses" using MM/YYYY as a dimension.

Thanks

Stu

Re: Sorting Help

pennetzdorfer — Fri, 14 Jun 2013 10:47:22 GMT

I think a 'NoConcatenate' just before your resident load will help you out:

Extract of Qlikview Manual:

The NoConcatenate prefix forces two tables with identical field sets (i.e. they would normally be concatenated automatically) to be treated as two separate internal tables.

Hope that helps!

Regards,

Florian

Re: Sorting Help

stuwannop — Fri, 14 Jun 2013 11:21:49 GMT

Thanks Florian that seems to work. It didn't solve my ulitimate problem but that's the solution to make sure the table doesn't get dropped.

Re: Sorting Help

pennetzdorfer — Fri, 14 Jun 2013 11:39:14 GMT

I guess createdon is your date field? Try to add a new field in your resident load:

date(MonthStart(createdon),'MM/YYYY') as Createdon_MonthYear

Then use createdon_MonthYear as dimension in your Chart.

Regards,

Florian

Re: Sorting Help

stuwannop — Fri, 14 Jun 2013 11:47:54 GMT

Great thanks that seemed to work. I put a line in my resident load but it didn't like it this one seems to work better. Once you change the format of a date and want to group/sort them qlik becomes very awkward!!

Thanks again for your help.

Stu