Make one record from key COA values from PL statment

[#1]

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

[PrashantSangle]

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

[Anonymous]

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

[#4]

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 .

[Anonymous]

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.

[#6 · Accepted solution]

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;