Need Help?

[paulwalker]

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

[datanibbler]

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   

[paulwalker]

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

[datanibbler]

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

[paulwalker]

Hi Nibbler,

if i have 2 record not a problem this solution....

suppose i have more than 100 records than ....

Any other solution...............

[datanibbler]

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

[vinay_hg]

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.

[ashfaq_haseeb]

Hi,

Marco Wedel is the man for this request.

Regards

ASHFAQ

[MarcoWedel]

Props to Ashfaq Mohammed: Thank You!

---

Thanks for the honor

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

[MarcoWedel]

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