Nested If Not working as Expected

[sekharQV]

Hi All,

I have created nested if condition to create a new field as shown in below script.

if(OrderNo<>0 and Match(Type,'INV','PO'),'POInvoice',

if(OrderNo=0 and Type='INV', 'NON PO',

if(Match(Type,'INV','PO'),'All')))  as InvoiceType.

Here filter is working fine for first two options 'POInvoice', and 'NON PO'  but failing for 3rd option 'All' (It is filtering Only 'PO' value in Type field.

Note:Please find attached files for your reference.

To Achieve this i have performed concatenation of Tables by creating each of the option in each concatenation as shown below and it is working fine.

Filter:

LOAD OrderNo,

    Type

FROM

[..\Filter\Filter.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate(Filter)

LOAD OrderNo,Type,

if(OrderNo<>0 and Match(Type,'INV','PO'),'POInvoice') as InvoiceType

FROM

[..\Filter\Filter.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate(Filter)

LOAD OrderNo,Type,

if(OrderNo=0 and Type='INV', 'NON PO') as InvoiceType

FROM

[..\Filter\Filter.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate(Filter)

LOAD OrderNo,Type,

if(Match(Type,'INV','PO'),'All') as InvoiceType

FROM

[..\Filter\Filter.xlsx]

(ooxml, embedded labels, table is Sheet1);

But it seems like heavy process and impacts the performance.

Anybody can please suggest the best way to create the desired filter?

Thanks in Advance,

Raja.

[sunny_talwar]

Not sure, but may be you need this

Filter:

LOAD OrderNo,

    Type

FROM

[Filter.xlsx]

(ooxml, embedded labels, table is Sheet1);

FilterTemp:

LOAD OrderNo,Type,

'POInvoice' as InvoiceType

FROM [Filter.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where OrderNo <> 0 and Match(Type, 'INV', 'PO');

Concatenate(FilterTemp)

LOAD OrderNo,Type,

'NON PO' as InvoiceType

FROM [Filter.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where OrderNo = 0 and Type = 'INV';

Concatenate(FilterTemp)

LOAD OrderNo,Type,

'All' as InvoiceType

FROM [Filter.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Match(Type,'INV','PO');

Left Join (Filter)

LOAD *

Resident FilterTemp;

DROP Table FilterTemp;

[Anil_Babu_Samineni]

What performance you are expecting. I think sunny way is same as you. But, Not sure how u want to show in performance?

[sekharQV]

Hi Anil,

My fact table size is 6 Million records and i am already doing some transformations while loading data which will slowdown script load time to perform these all calculations....

so to achieve my requirement, i have to load same table almost 4 times which may increase script loading time.

And this is my assumption only.

Please correct me if i am wrong.

Thanks,

Raja.

[RadovanOresky · Accepted solution]

Hi,

In your initial IF() statement, the 'INV' types are already solved by first two conditions > OrderNo is either 0 or not > so there are no INV types to be included in 'All'.

The only way how to make some records be described by multiple values is by expanding the dataset, which you did with the concatenation and joining.

This is "performance heavy", but, in case you really need to have the 'All' value for selection, I believe you just need to concatenate it once:

Filter:

LOAD OrderNo,

    Type,

    if(OrderNo<>0 and Match(Type,'INV','PO'),'POInvoice',

          if(OrderNo=0 and Type='INV', 'NON PO'))  as InvoiceType.

FROM

[Filter.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate (Filter)

LOAD OrderNo,Type,

     'All' as InvoiceType

FROM [Filter.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Match(Type,'INV','PO');

[Anil_Babu_Samineni]

True, But to do Transformation you should need to resident from one to different via splitting for business. So, Yo cannot manipulate the build engine which already exist.

Off course, To main transformation you should use another QVW rather single QVW to get more performance using 3-Tier architecture. Are you maintain 3-tier architecture? If not, Please do practice and create final table into QVD then load that QVD