My AppMap recode with condition not return correct value.

[paulyeo11]

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

[avinashelite]

what is the data your excepting ?? could you please share the excel files also so that we could check the same

[paulyeo11]

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

[avinashelite]

The site is blocked , attache the files directly

[paulyeo11]

Hi Avinash

Enclosed is my zip file.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

[avinashelite]

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

[paulyeo11]

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

[paulyeo11]

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

[avinashelite · Accepted solution]

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;

[paulyeo11]

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