topic Re: My AppMap recode with condition not return correct value. in QlikView

My AppMap recode with condition not return correct value.

paulyeo11 — Wed, 18 Jan 2017 05:23:11 GMT

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

Re: My AppMap recode with condition not return correct value.

avinashelite — Wed, 18 Jan 2017 06:12:03 GMT

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

Re: My AppMap recode with condition not return correct value.

paulyeo11 — Wed, 18 Jan 2017 06:26:05 GMT

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

Re: My AppMap recode with condition not return correct value.

avinashelite — Wed, 18 Jan 2017 06:29:34 GMT

The site is blocked , attache the files directly

Re: My AppMap recode with condition not return correct value.

paulyeo11 — Wed, 18 Jan 2017 06:35:05 GMT

Hi Avinash

Enclosed is my zip file.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

Re: My AppMap recode with condition not return correct value.

avinashelite — Wed, 18 Jan 2017 07:14:59 GMT

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

Re: My AppMap recode with condition not return correct value.

paulyeo11 — Wed, 18 Jan 2017 07:22:29 GMT

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

Re: My AppMap recode with condition not return correct value.

paulyeo11 — Wed, 18 Jan 2017 07:56:05 GMT

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

Re: My AppMap recode with condition not return correct value.

avinashelite — Wed, 18 Jan 2017 08:50:15 GMT

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;

Re: My AppMap recode with condition not return correct value.

paulyeo11 — Wed, 18 Jan 2017 08:56:05 GMT

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

Re: My AppMap recode with condition not return correct value.

avinashelite — Wed, 18 Jan 2017 09:18:32 GMT

Ok , let me explain

*First, all your conditions are fine

*You have created the sales_G table and liking the sales table via grouping

*grouping field doesn't have the unique key because of which it showing all the related files associated via

grouping

*What is did is in the new table sales_G I created the NEW_SOURCE so that will get the right combination

one more way to do this is

in the sales table create a key like

SOURCE&'-'&grouping as KEY

and create similar key and an indicator like below in sales_G table

SOURCE&'-'&grouping as KEY,

'sales_G ' as Indicator,

and use the Indicator in set analysis to get the rite data ...

Hope you understand now.

Re: My AppMap recode with condition not return correct value.

paulyeo11 — Wed, 18 Jan 2017 09:24:53 GMT

Hi Avinash

Wow your understanding of data model is very well. In the first place how do know figure out that link issue ? I just want to learn from you how you do trouble shooting.

Does the way I do the recode it is call partial reload or resident load ?

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

Re: My AppMap recode with condition not return correct value.

avinashelite — Wed, 18 Jan 2017 09:33:35 GMT

It comes by experience

*First I checked how is the data

*Then saw what the results your getting , you had both the values and then I checked the mapping key and got the issue from their

I suggest you to verify it step by step the is the best approach

if you have got the answer mark the correct answer and close this thread

Re: My AppMap recode with condition not return correct value.

paulyeo11 — Wed, 18 Jan 2017 10:28:50 GMT

Hi Sir

Sorry to trouble you again.

I just got internet , and i have check the result . I notice that the result is still wrong.

Kindly note that GROUP_CLASS1 return correct value , but GROUP_CLASS3 return wrong value.

As when grouping = 1 , for Q_DOD_2016 should be recode to G1-SG

As when grouping = 1 , for Q_FCKL09 should be recode to G1-MY

Kindly take a look at the enclosed QVW. Refer to Table 1. I am using the QVW you send me.

Thank you for your help.

Paul

Re: My AppMap recode with condition not return correct value.

avinashelite — Wed, 18 Jan 2017 10:54:43 GMT

check this one

Re: My AppMap recode with condition not return correct value.

paulyeo11 — Wed, 18 Jan 2017 12:28:58 GMT

Hi Avinash

I saw you create a key to link the 2 table. Thank you very much finally now working fine , i can sleep well .

Paul

Re: My AppMap recode with condition not return correct value.

avinashelite — Wed, 18 Jan 2017 13:02:55 GMT

Aha nice ..have a good sleep