Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_saini
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
sunny_talwar

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
sunny_talwar

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

its_anandrjs

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

Find the attached application hope you are looking for this.

amit_saini
Master III
Master III
Author

Yes Sunny!

amit_saini
Master III
Master III
Author

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

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

May be like attached

sunny_talwar

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
Specialist II
Specialist II

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