topic Re: Make one record from key COA values from PL statment in QlikView

Make one record from key COA values from PL statment

Fri, 27 Feb 2015 10:53:56 GMT

Hi !

I have PL statement datas from more than 10,000 companys ,

But I just need several account values. So I would like to make a one record from multiple records for each company.

I really hope experts guides .

Best regards

Jei H. Cho

The source data

ID COA Value

1 001 1,000

1 002 2,000

1 003 3,000

1 004 1,111

2 001 11,000

2 002 22,000

2 003 32,000

2 004 11,111

Wanted data

ID 001value 002value

1 1,000 2,000

2 11,000 22,000

Re: Make one record from key COA values from PL statment

PrashantSangle — Fri, 27 Feb 2015 12:25:17 GMT

Hi,

If you want to do this in front end.

Create Pivot table chart

take ID and COA as dimension

and take Sum(Value) as expression

then drag your COA dimension from Vertical to Horizontal i.e. drag it to top of the Header.

Also In presentation tab->select Always fully expanded.

Regards

Re: Make one record from key COA values from PL statment

Anonymous — Fri, 27 Feb 2015 12:34:50 GMT

See if this gives you what you want:

source_data:
GENERIC
LOAD * INLINE [
ID,   COA,   Value
1,    001,       '1,000'
1,    002,       '2,000'
1,    003,       '3,000'
1,    004,       '1,111'
2,    001,     '11,000'
2,    002,     '22,000'
2,    003,     '32,000'
2,    004,     '11,111'
];

Re: Make one record from key COA values from PL statment

Mon, 02 Mar 2015 09:01:00 GMT

Thank you for respose.

Now the COAs are 400 . and the companys are more than 20,000 .

Now I designed fact table . In the fact table one rows represents one company .

For each company have PL statment and balance sheet . The maximum record of each company are 400 rows.

But for the analysis I just need four rows of PL statement , balace sheet for each company. So after catching four values of PL statement and balance sheets , I will join the catched fields to fact tables .

The key of solution is to catch the just four fields by filtering .

Re: Make one record from key COA values from PL statment

Anonymous — Mon, 02 Mar 2015 19:41:40 GMT

Sorry, I don't understand... Maybe you can upload a small sample of your application with the problem description.

Or better yet, find a local QlikView consultant to help you out.

Re: Make one record from key COA values from PL statment

Thu, 05 Mar 2015 09:45:05 GMT

Hi . I solved this problem according to other reference . The answer is generic load .

But before generic load , I just need the specific COA code value .

I used where clause statement .

Thank you for every qlik community members.

Example =====

TempBalance :

generic

Load

ID,COA,Value

resident [source data ]

where COA ='001' or COA = '002' ;

Balance :
load distinct
ID
resident [Source data ] ;

FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'TempBalance.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (Balance) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i

DROP TABLE TableList;