Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have one table, i want to convert into Cross table,
Name | SalesPerson | FTD | MTD | YTD | FTD | MTD | YTD | FTD | MTD | YTD |
Satyam | Raju | 22,675 | 1,025,432 | 4,856,952 | - | 69,524 | 1,111,426 | 22,675 | 1,025,432 | 4,856,952 |
Microsoft | Sunil | 646 | 829,684 | 3,433,276 | - | 9,197 | 514,329 | 646 | 829,684 | 3,433,276 |
Total Close Out | 23,321 | 1,855,115 | 8,290,228 | - | 78,721 | 1,625,755 | 23,321 | 1,855,115 | 8,290,228 |
This is my table, here first FTD, MTD, YTD into one field like
First FTD, MTD and YTD as Sales
Second FTD, MTD and YTD as Margin
Third FTD, MTD and YTD as GrossMargin
Hope you understand...
Thanks in Advance
Hi Paul,
if I understand you correctly, you want a table with three dimensions:
- "Scope" (FTD, MTD, YTD)
- "category" (Sales, Margin, GrossMargin)
- "name"j
=> You could have that as a pivot_table. In a regular bar- or combo-chart, more than two dimensions are possible, but maybe not advisable as the charting_area then gets really cramped.
The method I will propose might not be the most elegant one, but I tend to go for the one that is easier to understand for others.
=> In short words, to have that, you need to convert all the necessary fields into dimensions, you will need a lot of RESIDENT LOADs. You can load it altogether in a first step and then have a series of RESIDENT LOADs, each time hard-coding all the values you want to use as dimensions later and modifying one of them -> like:
LOAD
'Satyam' as name,
'Sales' as category,
'YTD' as scope,
[YTD] as Data (this is the field holding the actual figures)
RESIDENT [primary_table];
// Next one - will be concatenated automatically
LOAD
'Satyam' as name,
'Sales' as category,
'MTD' as scope,
[MTD] as Data (this is the field holding the actual figures)
RESIDENT [primary_table]
... and so on - for 3 dimensions, you will probably need 3x3=9 RESIDENT LOADs like these.
HTH
Best regards,
DataNibbler
Hi Nibbler,
No, i have 2 dimensions Name and Sales Person,
just i have assigned Sales, Margin and GrossMargin
i need to create 1 dimension Category(Gross, Margin and GrossMargin)
i need to convert first 3(FTD, MTD, YTD) Sales
second 3(FTD, MTD, YTD) Gross and
third 3(FTD, MTD, YTD) GrossMargin
Hi Paul,
well, the system will remain the same. So you have 2 dimensions, so you need 2x2=4 RESIDENT LOADs,
each time hard-coding these two fields and modifying one of them -> like
LOAD
'Satyam' as name,
'Raju' as Salesperson,
[YTD_Sales],
[MTD_Sales],
...
RESIDENT [primary_table];
// Next one - will be concatenated
LOAD
'Satyam' as name,
'Sunil' as Salesperson,
[YTD_Sales],
[MTD_Sales],
...
RESIDENT [primary_table];
// Next one - will be concatenated
... and so on
Hi Nibbler,
if i have 2 record not a problem this solution....
suppose i have more than 100 records than ....
Any other solution...............
Hi Paul,
the nr. of records to load should not be an issue? I don't know exactly what you mean.
However, that is the solution I would propose.
QlikView does also have a crosstable_wizard that should be able to load your table as two-dimensional - it is implemented in the general LOAD_wizard, just click through the process and have a look - but I'm not so sure about that - like I said, I prefer to code the "slower" way so the construct is easier for others to understand.
Best regards,
DataNibbler
cant you rename the FTD,MTD,YTD of sales to SFTD,SMTD,SYTD and remaining so on the just use this field from same table.
I'll try my very best.
How about:
table1:
CrossTable (FieldName, FieldValue, 2)
LOAD * FROM [http://community.qlik.com/thread/132447] (html, codepage is 1252, embedded labels, table is @1);
Left Join (table1)
LOAD Distinct
FieldName,
PurgeChar(FieldName, '0123456789') as FieldName2,
Pick(NumSum(KeepChar(FieldName, '0123456789'),1),'Sales','Margin','GrossMargin') as Type
Resident table1;
table2:
Generic LOAD
Name,
SalesPerson,
Type,
FieldName2,
FieldValue
Resident table1;
DROP Table table1;
hope this helps
regards
Marco
without loading the "total close out" row but calculating the total instead:
table1:
CrossTable (FieldName, FieldValue, 2)
LOAD * FROM [http://community.qlik.com/thread/132447] (html, codepage is 1252, embedded labels, table is @1);
Left Join (table1)
LOAD Distinct
FieldName,
PurgeChar(FieldName, '0123456789') as FieldName2,
Pick(NumSum(KeepChar(FieldName, '0123456789'),1),'Sales','Margin','GrossMargin') as Type
Resident table1;
table2:
Generic LOAD
Name,
SalesPerson,
Type,
FieldName2,
FieldValue
Resident table1
Where not Name like 'Total*';
DROP Table table1;
hope this helps
regards
Marco