Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
IF(Lower(Category) = 'interfaces' and Lower(Subcategory) = 'millwork', 'Millwork',
IF(Lower(Category) = 'loftware' and Len(Trim(Subcategory))=0, 'Loftware')) as Real_Category
IF(Lower(Category) = 'interfaces' and Lower(Subcategory) = 'millwork', 'Millwork',
IF(Lower(Category) = 'loftware' and Len(Trim(Subcategory))=0, 'Loftware')) as Real_Category
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?
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);
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);
This was it. Thank you!