topic Re: Having some issues flagging a transaction in QlikView

Having some issues flagging a transaction

Mon, 10 Dec 2012 20:28:08 GMT

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

Re: Having some issues flagging a transaction

Mon, 10 Dec 2012 23:42:42 GMT

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

Re: Having some issues flagging a transaction

Tue, 11 Dec 2012 13:00:20 GMT

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?

Re: Having some issues flagging a transaction

Tue, 11 Dec 2012 13:59:05 GMT

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>

Re: Having some issues flagging a transaction

Tue, 11 Dec 2012 14:45:57 GMT

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

I uploaded the updated QVW

Re: Having some issues flagging a transaction

Tue, 11 Dec 2012 23:15:39 GMT

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

Re: Having some issues flagging a transaction

Tue, 11 Dec 2012 23:36:49 GMT

Sergio,

thank you for your help.

Re: Having some issues flagging a transaction

Fri, 14 Dec 2012 21:37:48 GMT

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?

Re: Having some issues flagging a transaction

Fri, 14 Dec 2012 21:50:44 GMT

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>

Re: Having some issues flagging a transaction

Fri, 14 Dec 2012 22:28:46 GMT

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?

Re: Having some issues flagging a transaction

Sat, 15 Dec 2012 01:05:51 GMT

Josep,

//Bigtable with all the data:

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];

//tinytable to flag

test1:

noconcatenate

LOAD

[GL Acct],

Reference,

resident test ;

left join load

Reference,

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

resident test

group by Reference ;

Flaged:

noconcatenate

Reference,

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

resident test1;

drop table test1;

MAPFLAG:

mapping load Reference,FlagRef resident Flaged;

drop table Flaged

FinalBigTable:

applymap('MAPFLAG',Reference) as FlagREF;

load * resident test;

drop table test;

Untested.The idea is to solve the flag and then map the original table, so at the end you have all the columns of the begining andda new column that defines the flag.

Hope helps.

Sergio