Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
Need help on below scenario:
I'm having 30 thousand records like below
PSD | Number | Sales |
91002 (a) | 123 | 10 |
91002 (c) | 123 | 20 |
91002 (gs) | 123 | 30 |
91002 | 123 | 40 |
91002 (ngs) | 123 | 50 |
91002 (pc) | 123 | 60 |
91002 (pgs) | 123 | 70 |
91002 (pngs) | 123 | 80 |
91002 (re) | 123 | 90 |
91002 (sun) | 123 | 100 |
91004 (a) | 456 | 110 |
91004 (c) | 456 | 120 |
91004 (gs) | 456 | 130 |
91004 | 456 | 140 |
91004 (ngs) | 456 | 150 |
91004 (pc) | 456 | 160 |
91004 (pgs) | 456 | 170 |
91004 (pngs) | 456 | 180 |
91004 (re) | 456 | 190 |
91004 (sun) | 456 | 200 |
91002 | 123 | 210 |
91004 | 456 | 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:
PSD | Number | Sales |
91002 | 123 | 210 |
91002 (c) | 123 | 20 |
91002 (pc) | 123 | 60 |
91004 | 456 | 220 |
91004 (c) | 456 | 120 |
91004 (pc) | 456 | 160 |
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
My bad, try this
WildMatch(PSD, '*(c)', '*(pc)', '')/1E4 + Num#(KeepChar(PSD, '0123456789'))
RangeSum(WildMatch(PSD, '*(c)', '*(pc)', '')/1E4, Num#(KeepChar(PSD, '0123456789')))
So filter out only the 5 digit number and the associated c and pc value in the script?
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:-
Find the attached application hope you are looking for this.
Yes Sunny!
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
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.
May be like attached
My bad, try this
WildMatch(PSD, '*(c)', '*(pc)', '')/1E4 + Num#(KeepChar(PSD, '0123456789'))
RangeSum(WildMatch(PSD, '*(c)', '*(pc)', '')/1E4, Num#(KeepChar(PSD, '0123456789')))
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;