Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have below script with the help from Staffen and HIC which using loop to load multiple raw data , is work fine.
FOR i=0 TO NoOfRows('FileList_Sales') -1
LET vFile = PEEK('FileName_Sales', $(i), 'FileList_Sales');
For each vFileName in FileList('$(vRAWPath)*.txt')
if(subfield(subfield('$(vFileName)','\',-1),'.',1) = '$(vFile)') then
sales:
LOAD
FileBaseName() as SOURCE,
'SALES' as table,
@50:60T as [date],
@10:38T as [COMPANY],
@307:312T as [grouping],
ApplyMap('MapGroup_SG',@307:312T) as [GROUP_CLASS_TDS],
@121:133T as [cost]
FROM "$(vFileName)" (ansi, fix, no labels, header is 0, record is line);
endif
Next vFileName
next i
My issue is when i using below script to recode grouping to GROUP_CLASS_3 Is not return correct value.
sales_G:
LOAD grouping,
if (SOURCE='Q_DOD_2016',ApplyMap('MapGrouping_SG',grouping),
if (SOURCE='Q_FCKL09',ApplyMap('MapGrouping_MY',grouping),
)) as [GROUP_CLASS_3]
resident sales;
Hope you can advise me.
enclosed my QVW
Paul
Try like the attached file you will get the correct results
sales_G:
LOAD grouping,
SOURCE as NEW_SOURCE,
if (SOURCE='Q_DOD_2016',ApplyMap('MapGrouping_SG',grouping),
if (SOURCE='Q_FCKL09',ApplyMap('MapGrouping_MY',grouping),
)) as [GROUP_CLASS_3]
resident sales;
what is the data your excepting ?? could you please share the excel files also so that we could check the same
Hi Sir
Thank you very much for your help , Below is the link you can download my QV DOC with raw data for testing, pls unzip and copy to C:\RECODE_GROUP folder :-
https://www.dropbox.com/s/civp4bt9i23p7ie/RECODE%20GROUP%20AT%20LOAD%20SCRIPT_.zip?dl=0
My expectation I show it at QVW sheet. When SOURCE = Q_DOD_2016 , I expect to see GROUP_CLASS3 = G1-SG , when grouping = 1.
Paul Yeo
DIrector
TDS Technology (S) P/L
Whatsapp +659:261804
The site is blocked , attache the files directly
Hi Avinash
Enclosed is my zip file.
Paul Yeo
DIrector
TDS Technology (S) P/L
Whatsapp +659:261804
I checked your files you don't have the proper the keys for mapping ..
You have the duplicate data and hence your seeing the data for both ..add the GROUP_CLASS3 as list box and check the G1-SG you will see the data ...avoid the duplicate data
Hi Avinash
May I know why date got to do with field name grouping & GROUP_CLASS3 ?
As now I only need to recode my field from grouping to GROUP_CLASS3 , it will not affect by date . Date will be affect if I want to do compute sales.
The 2 data file is filter by SOURCE field. Again it will not link to date.
Hope you can share with me why date field is link ?
Paul Yeo
DIrector
TDS Technology (S) P/L
Whatsapp +659:261804
Hi Avinash
I forget to mention to you, when I recode grouping with out add condition , I can recode with out any issue for individual SOURCE table, I get issue when I do Mapping with condition.
If I doing Mapping with out condition , the field name for 2 SOURCE table must be unique , so I cannot have 2 seperate field name for GROUP_CLASS .
Paul Yeo
DIrector
TDS Technology (S) P/L
Whatsapp +659:261804
Try like the attached file you will get the correct results
sales_G:
LOAD grouping,
SOURCE as NEW_SOURCE,
if (SOURCE='Q_DOD_2016',ApplyMap('MapGrouping_SG',grouping),
if (SOURCE='Q_FCKL09',ApplyMap('MapGrouping_MY',grouping),
)) as [GROUP_CLASS_3]
resident sales;
Hi Avinash
Thank you very much for your help.
Can you explain to me what need to need to SOURCE as NEW_SOURCE ?
Paul Yeo
DIrector
TDS Technology (S) P/L
Whatsapp +659:261804