Cross Table Data Conversion

[ramyasaiqv]

Hello Community,

I have an excel  and tried to convert it but still having some issue and need some help.

My issue is in the attached excel and qvw , I have a field named “Category” which has 2 rows (Total and Dynamic) of data and when I convert using cross table prefix I’m getting  the first row (Total) but not the second row “Dynamic”. I’m not if it’s possible to bring the 2^nd row (Dynamic) data also. I need some help in how to handle this issue. Thank you,

Ramya

[Anil_Babu_Samineni · Accepted solution]

I would always ask you to do this with in excel only, Because it is really headache to work for UN-structured data set.

Here, you can do some thing like below rather yours

My preference is this?

CrossTable(MonthNames, Data, 5)

LOAD Num, [Customer Grp], Customer, Category, Ref, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct,

    Nov, Dec

FROM

(ooxml, embedded labels, table is Test, filters(

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null))

));

[ramyasaiqv]

Hi Anil,

It worked. Yes, i do understand it's better to convert in the excel itself . Unfortunately  i have a bunch of excels with unstructured data and  struggling to format all of them. Thank you so much for your help.

Ramya