Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am new to QV and would like to ask if there is a macro function or simple code/s that can handle this. Please do note that distinct value for Type can change every month, hence a dynamic code that can handle this future requirement is much preferrable. But if there is a static codes that requires user revision in case of any changes in the raw date, that would be great if you can share as well.
Thank you very much.
RAW DATA
Category | Type | Date | Value |
---|---|---|---|
Apple | X | Jan 6, 2013 | 5 |
Apple | X | Jan 16, 2013 | 10 |
Apple | Y | Jan 6, 2013 | 100 |
Apple | Y | Jan 16, 2013 | 200 |
Bear | X | Jan 6, 2013 | 10 |
Bear | X | Jan 16, 2013 | 15 |
Bear | Y | Jan 6, 2013 | 50 |
Bear | Y | Jan 16, 2013 | 100 |
DESIRED OUTPUT
Category | Date | X | Y | Heade 5 |
---|---|---|---|---|
Apple | Jan 6, 2013 | 5 | 100 | |
Apple | Jan 16, 2013 | 10 | 200 | |
Bear | Jan 6, 2013 | 10 | 50 | |
Bear | Jan 16, 2013 | 15 | 100 | |
Hi Joy,
You may try this script:
[DS]:
LOAD * Inline [
Category , Type , Date , Value
Apple , X , 06-Jan-13 , 5
Apple , X , 16-Jan-13 , 10
Apple , Y , 06-Jan-13 , 100
Apple , Y , 16-Jan-13 , 200
Bear , X , 06-Jan-13 , 10
Bear , X , 16-Jan-13 , 15
Bear , Y , 06-Jan-13 , 50
Bear , Y , 16-Jan-13 , 100];
[TMP1]:
GENERIC LOAD Category&'|'& Date AS [tmpCategory],Type,Value RESIDENT [DS];
[RESULT]:
LOAD DISTINCT Category&'|'& Date AS [tmpCategory] RESIDENT [DS];
DROP TABLE [DS];
FOR i = 0 to NoOfTables()
TableList:
LOAD '[' & TableName($(i)) &']' AS Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'TMP1.*');
NEXT i
FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN ([RESULT]) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i
[FinalResult]:
NoConcatenate
LOAD
SubField([tmpCategory],'|',1) AS [Category],
SubField([tmpCategory],'|',2) AS [Date],
*
Resident [RESULT];
DROP Field [tmpCategory];
Drop Table TableList,RESULT;
See attached file also.
Regards,
Sokkorn
Hi,
PFA file for solution. I did this by using Generic load in script. We can also do this by using pivot table and dragging the Type column to the top the header.
Hope this helps you.
Regards,
Jagan.
The way to do this imho is to load the raw data as it is and create a pivot table to show the desired output. Create a pivot table with Category, Date and Type as dimensions (in that order) and sum(Value) as expression. On the Presentation tab of the properties window of the pivot table enable the option Always Fully Expanded. Now you need to move the Type dimension to above the expression. Drag the column label to the right above the expression label. When you see a thick blue double-arrowed line you can let go. The Type dimension should now be displayed horizontally.