topic Re: Create new table, based on fields in excel doc in QlikView

Create new table, based on fields in excel doc

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Create new table, based on fields in excel doc

MK_QSL — Mon, 03 Jul 2017 14:25:50 GMT

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

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

Re: Create new table, based on fields in excel doc

Mon, 03 Jul 2017 14:28:45 GMT

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?

Re: Create new table, based on fields in excel doc

MK_QSL — Mon, 03 Jul 2017 14:32:02 GMT

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);

Re: Create new table, based on fields in excel doc

jaumecf23 — Mon, 03 Jul 2017 14:33:38 GMT

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);

Re: Create new table, based on fields in excel doc

Mon, 03 Jul 2017 14:46:40 GMT

This was it. Thank you!