Having some issues flagging a transaction

[#1]

I want to be able to identify reference numbers that include the [GL Acct] 200000

by creating a flag.

However if a reference number includes the GL Acct 200000, I want to flag the entire transaction (not just the line item that includes GL acct 200000)

so that i can see the other accounts that have the same reference number too (and apprear on the same transaction reference number).

For example see below:

Flagged

Reference #    GL Acct       Amount

818000           200000        200.00

818000           912000        -100.00

818000           812000        -100.00

Unflagged

Reference #    GL Acct       Amount

818009           310000        500.00

818009           814000        -300.00

818009           902440        -200.00

I have uploaded a QVD to see where i am running into problems and can not get the desired result

[#2]

Joseph,

You want to flag the header (reference) based on details of the lines (GL ACCT).

Group by header and apply the flag..

Try

test:

load * inline [

Reference,    GL Acct  ,     Amount,

818000   ,        200000 ,       200.00,

818000   ,        912000 ,       -100.00,

818000    ,       812000 ,       -100.00,

818009    ,       310000 ,       500.00,

818009     ,      814000  ,      -300.00,

818009    ,       902440  ,      -200.00,

897899,200000,300,

897899,912000,600,

895656,812000,560,

895656,310000,-456,];

test1:

noconcatenate LOAD [GL Acct],

     Reference,

    Amount

resident test ;

left join   load

Reference

,concat([GL Acct],'|') as Concat resident test

group by Reference ;

drop table test;

Flaged:

noconcatenate load [GL Acct],Reference,if(wildmatch (Concat,'*200000*')>0,'Flag','Others') as FlagRef,Amount

resident test1;

//drop table test1;

Hope it helps.

Sergio

[#3]

Sergio,

thank you for your help.

Your uplaoded QVW works and accomplishes what i needed however when i diive into the script i noticed that your uploaded QVW creates a synthetic key.

could you please suggest a correction?

Also, is there an alternate approach that would work just as well too?

[#4]

Joseph.

Uncomment the last drop table and that will do.

El 11/12/2012 10:00, "Joseph Bustos" <qcwebmaster@qlik.com> escribió:

**

QlikCommunity <http://community.qlik.com/index.jspa> Re:

Having some issues flagging a transaction created by Joseph Bustos<http://community.qlik.com/people/jsphbustos>in

Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/290258#290258>

[#5]

I uncommented the last drop table and reloaded and in table viewer the synthetic key still exists

I uploaded the updated QVW

[#6 · Accepted solution]

Josep,

The code is=>

test1:

noconcatenate LOAD [GL Acct],

     Reference,

    Amount

resident test ;

left join   load

Reference

,concat([GL Acct],'|') as Concat resident test

group by Reference ;

drop table test;

Flaged:

noconcatenate load [GL Acct],Reference,if(wildmatch (Concat,'*|200000|*')>0,'Flag','Others') as FlagRef,Amount

resident test1;

drop table test1;

The qvw is now working.Just missing the drop table name on the qvw, but not on the code.

Hope it helps.

Sergio

[#7]

Sergio,

thank you for your help. 

[#8]

Sergio if i had l 10 more columns of data, how would this affect the below script

The other fileds are useful but only the Reference, GL Acct and Amount are relevent to determine if they should all appear on the same transaction

I the added the below to the original script but cant get it to work

test:

LOAD

Trim(REFER) as Reference, 

Trim(ACT) AS [GL Acct],

AMTDOL as Amount,

[Field 1],

[Field 2],

[Field 3],

[Field 4],

[Field 5],

[Field 6],

[Field 7],

[Field 8],

[Field 9],

[Field 10];

test1:

noconcatenate

LOAD*,

[GL Acct],

     Reference,

    Amount

resident test ;

left join  

load

Reference,

concat([GL Acct],'|') as Concat

resident test

group by Reference ;

drop table test;

Flaged:

noconcatenate

load [GL Acct],

Reference,

if(wildmatch (Concat,'*|200000|*')>0,'Flag','Others') as FlagRef,Amount

resident test1;

drop table test1;

I Keep getting the message FIELD NAMES MUST BE UNIQUE WITHIN TABLE TEST1

how can i correct this?

[#9]

Josep,

Load * Loads everything.

And you are using LOAD*,

,

Reference,

Amount

resident test ;

So trying to load twice GL Acct,Reference and Amount. QV does not allow because AQL. Remember Associative

Change de names or just write down all the names of the fields .

Hope helps

Sergio

De: Joseph Bustos

Enviado el: viernes, 14 de diciembre de 2012 18:38

Para: SergioABR

Asunto: - Re: Having some issues flagging a transaction

<http://community.qlik.com/index.jspa> QlikCommunity

Re: Having some issues flagging a transaction

created by Joseph Bustos <http://community.qlik.com/people/jsphbustos> in Development (QlikView Desktop) - View the full discussion <http://community.qlik.com/message/291868#291868>

[#10]

Yes, I was using load becasue I have 10 more columns (dimensions) in my file that i would like to include however the 10 other columns do not determine transaction groupings.

SInce i have 10 other columns would i run the inline load and just include the additonal 10 columns or it will not work?