Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master III
Master III

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
Highlighted

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

View solution in original post

11 Replies
Highlighted

Re: Filter and Sort order help

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

Highlighted

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

Highlighted

Re: Filter and Sort order help

Find the attached application hope you are looking for this.

Highlighted
Master III
Master III

Re: Filter and Sort order help

Yes Sunny!

Highlighted
Master III
Master III

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

Highlighted

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.

Highlighted

Re: Filter and Sort order help

May be like attached

Highlighted

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

View solution in original post

Highlighted
Specialist II
Specialist II

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