Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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'
];
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 .
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.
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;