Create new table, based on fields in excel doc

[#1]

Hi all.

I have an excel doc with Case#, Category, and Subcategory. (Note that there is always a case number, but not always a category/subcategory). Data is attached.

I need to create another table in QlikView, let's call it Real_Category.

So if category = 'interfaces' and subcategory = 'millwork', then I want to create a field in Real_Category called 'Millwork'.

Also, if category = 'loftware' but there is no field in subcategory, then create 'Loftware' in Real_Category.

Examples such as these, allowing for a combination of category/subcategory, creating a new table.

I would like a script solution.

Any help is appreciated, thank you!

[MK_QSL · Accepted solution]

IF(Lower(Category) = 'interfaces' and Lower(Subcategory) = 'millwork', 'Millwork',

IF(Lower(Category) = 'loftware' and Len(Trim(Subcategory))=0, 'Loftware')) as Real_Category

[#3]

Hi, where would I put this? Is this in a preceding load? I currently have this.

LOAD Account as Customer,

     Billable,

     [Billing Type],

     [Case #],

     Category,

     Subcategory,

     Closed, 

     Product,

     [Record Type],

     Status,

     [DMSi Account ID] as AlphaProdID,

     [Billing Amount Final],

     Case_Count,

         Date(ClosedDateNum, 'MM/DD/YYYY')  AS Case_Closed_Date,

         Date(CreatedDateNum, 'MM/DD/YYYY')  AS Case_Created_Date ;

LOAD Account,

     Billable,

     [Billing Type],

     [Case #],

     Category, 

     Subcategory,

     Num(Floor(Subfield([Closed Date], ',', 1))) AS ClosedDateNum,

     Closed,

     Num(Floor(Subfield([Created At], ',', 1))) AS CreatedDateNum,

     Product,

     [Record Type],

     Status,

     [DMSi Account ID],

     [Billing Amount Final],

     1 as Case_Count

FROM

(ooxml, embedded labels);

You can ignore the other fields, but would I put it in the preceding load? Or after both of these sections?

[MK_QSL]

LOAD Account as Customer,

     Billable,

     [Billing Type],

     [Case #],

     Category,

     Subcategory,

     Closed,

     Product,

     [Record Type],

     Status,

     [DMSi Account ID] as AlphaProdID,

     [Billing Amount Final],

     Case_Count,

         Date(ClosedDateNum, 'MM/DD/YYYY')  AS Case_Closed_Date,

         Date(CreatedDateNum, 'MM/DD/YYYY')  AS Case_Created_Date,

IF(Lower(Category) = 'interfaces' and Lower(Subcategory) = 'millwork', 'Millwork',

IF(Lower(Category) = 'loftware' and Len(Trim(Subcategory))=0, 'Loftware')) as Real_Category

;

LOAD Account,

     Billable,

     [Billing Type],

     [Case #],

     Category,

     Subcategory,

     Num(Floor(Subfield([Closed Date], ',', 1))) AS ClosedDateNum,

     Closed,

     Num(Floor(Subfield([Created At], ',', 1))) AS CreatedDateNum,

     Product,

     [Record Type],

     Status,

     [DMSi Account ID],

     [Billing Amount Final],

     1 as Case_Count

FROM

(ooxml, embedded labels);

[jaumecf23]

Do you need something like this maybe:

Real_Category:

LOAD [Case #],

     Category,

     Subcategory,

     if(Category='interfaces' and Subcategory='millwork','Y','N') as Millwork,

     if(Category='loftware' and len(trim(Subcategory))=0,'Y','N') as Loftware

FROM

[CaseCombine.xlsx]

(ooxml, embedded labels, table is Sheet1);

[#6]

This was it. Thank you!