Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

amit_saini
Not applicable

Filter and Sort order help

Hi Folks,

Need help on below scenario:

I'm having 30 thousand records like below

  

PSDNumberSales
91002 (a)12310
91002 (c)12320
91002 (gs)12330
91002 12340
91002 (ngs)12350
91002 (pc)12360
91002 (pgs)12370
91002 (pngs)12380
91002 (re)12390
91002 (sun)123100
91004 (a)456110
91004 (c)456120
91004 (gs)456130
91004 456140
91004 (ngs)456150
91004 (pc)456160
91004 (pgs)456170
91004 (pngs)456180
91004 (re)456190
91004 (sun)456200
91002123210
91004456

220

and have to filter the data by PSD in such a manner that first it should show from above data e.g 91002 ,91002 (c) and 91002(pc) , so onlt the 5 digit number followed by (c) value and last (pc) value.

I need to apply this filter before loading the data and once this is loaded I want Output like below:

 

PSDNumberSales
91002123210
91002 (c)12320
91002 (pc)12360
91004456220
91004 (c)456120
91004 (pc)456160

Note: In PSD field 5 digit number and their respective (c) and (pc) value can be random and not available always together , but we have to filter the data first and then need to show output as above.

Please help how this can be possible!

Thanks,

AS

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Filter and Sort order help

My bad, try this

WildMatch(PSD, '*(c)', '*(pc)', '')/1E4 + Num#(KeepChar(PSD, '0123456789'))


RangeSum(WildMatch(PSD, '*(c)', '*(pc)', '')/1E4, Num#(KeepChar(PSD, '0123456789')))

Capture.PNG

11 Replies
sunny_talwar
Not applicable

Re: Filter and Sort order help

So filter out only the 5 digit number and the associated c and pc value in the script?

its_anandrjs
Not applicable

Re: Filter and Sort order help

Hey,

Add on flag in your load script and when doing SUM in UI use expression

Expre:-

=Sum( if(len(PSD) = 5 Or PSDFlag = 1, Sales))

Fact:

LOAD PSD, SubField(PSD,' ',-1) as PSDSymbol,

     Number,

     Sales

FROM

D:\Qcomm\12092017\Data.xls

(biff, embedded labels, table is [Sheet1$]);

NewFact:

LOAD *,If(Match(PSDSymbol,'(c)','(pc)'),1,0) as PSDFlag

Resident Fact;

DROP Table Fact;


Output:-

OP.PNG

its_anandrjs
Not applicable

Re: Filter and Sort order help

Find the attached application hope you are looking for this.

amit_saini
Not applicable

Re: Filter and Sort order help

Yes Sunny!

amit_saini
Not applicable

Re: Filter and Sort order help

Thank you so much Anand for suggestion , but Output should be like below:

91002

91002(c)

91002(pc)

91004

91004(c)

91004(pc)

Also has data is huge , so don't want to try from properties sort option ,there should be an automated sort order!

Regards,

Amit

its_anandrjs
Not applicable

Re: Filter and Sort order help

Thanks try to another one dynamic table in the data model where it has sort order for (c),(pc) and null value after PSD value. Let me check this.

sunny_talwar
Not applicable

Re: Filter and Sort order help

May be like attached

sunny_talwar
Not applicable

Re: Filter and Sort order help

My bad, try this

WildMatch(PSD, '*(c)', '*(pc)', '')/1E4 + Num#(KeepChar(PSD, '0123456789'))


RangeSum(WildMatch(PSD, '*(c)', '*(pc)', '')/1E4, Num#(KeepChar(PSD, '0123456789')))

Capture.PNG

vvira1316
Not applicable

Re: Filter and Sort order help

Hi,

How about

NoConcatenate
DataFileTmp:
LOAD PSD,
Number,
SubField(PSD, ' ', 1) as [PSD_NumericPart],
SubField(PSD, ' ', 2) as [PSD_AlphaPart],
Sales
FROM
[..\Data\Data.xlsx]
(
biff, embedded labels, table is Sheet1$);

NoConcatenate
DataFile:
LOAD *,
AutoNumber(1, 'AlphaSort') as SortOrder
Resident DataFileTmp
where Len([PSD_AlphaPart]) = 0
Order by [PSD_NumericPart] asc
;
Concatenate
LOAD *,
AutoNumber(SubField(PSD, ' ', 2), 'AlphaSort') as SortOrder
Resident DataFileTmp
where [PSD_AlphaPart] <> Null()
Order by [PSD_NumericPart] asc, [PSD_AlphaPart] asc
;

DROP Table DataFileTmp;

PSD Table.PNGTableSort.PNG