topic Re: ODBC+Vlookup--Scripting Help in QlikView

ODBC+Vlookup--Scripting Help

Tue, 31 May 2016 19:20:46 GMT

Experts,

I need help creating the following application/script. I have attached a Qlik document and .xls for you.

Let me give you some background--

I currently run a revenue report from my ERP system that I convert into Excel. Once in Excel, I VLOOKUP that information against a separate file that sits inside my computer to find missing (blanks/zeros) information.

The data from the ERP system is the following:

The data, called 'Reason's, sitting on my computer is the following:

The normal operation of myself pivoting the information in Excel first looks like this:

^ Notice the blanks? I need the 'Reason's to populate here... so I do an IFERROR/VLOOKUP against the 'Reasons' in Excel for the below desired outcome:

All help very much appreciated!

Re: ODBC+Vlookup--Scripting Help

sunny_talwar — Wed, 01 Jun 2016 03:08:29 GMT

Try this:

Revenue:

LOAD [GL Date],

[Actual Amount],

[Phase/Matter Codes]

FROM

[Test1.xlsx]

(ooxml, embedded labels, table is Revenue);

Reasons:

CrossTable(Date, Value)

LOAD [Phase/Matter Codes],

[42517],

[42518]

FROM

[Test1.xlsx]

(ooxml, embedded labels, table is Reasons);

Join (Revenue)

LOAD [Phase/Matter Codes],

Date(Num#(Date)) as [GL Date],

Value

Resident Reasons;

FinalTable:

LOAD [Phase/Matter Codes],

[GL Date],

If(Len(Trim([Actual Amount])) = 0, Value, [Actual Amount]) as [Actual Amount]

Resident Revenue;

DROP Table Reasons, Revenue;

Pivot Table

Dimension:

[Phase/Matter Codes]

[GL Date]

Expression:

=If(Sum(Aggr(NODISTINCT Sum(If(IsNum([Actual Amount]), [Actual Amount])), [Phase/Matter Codes])) <> 0, If(SecondaryDimensionality() = 0 or Dimensionality() = 0, Sum(If(IsNum([Actual Amount]), [Actual Amount])), [Actual Amount]))

Re: ODBC+Vlookup--Scripting Help

Wed, 01 Jun 2016 14:42:26 GMT

Sunny-

In the 'Reasons' excel tab, I am trying to add names to the Phase/Matter Codes. I have attached the spreadsheet. I am having trouble with adding that information to your solution.

Re: ODBC+Vlookup--Scripting Help

sunny_talwar — Wed, 01 Jun 2016 15:42:04 GMT

How does name impact the final solution?

Re: ODBC+Vlookup--Scripting Help

Wed, 01 Jun 2016 15:51:59 GMT

It does not. I was trying to correlate the Phase/Matter codes to those specific name fields, but I am running into errors.

Your answer is correct--thank you very much!

Re: ODBC+Vlookup--Scripting Help

sunny_talwar — Wed, 01 Jun 2016 17:10:24 GMT

May be check this:

Revenue:

LOAD [GL Date],

[Actual Amount],

[Phase/Matter Codes]

FROM

[Test1 (1)NAMES.xlsx]

(ooxml, embedded labels, table is Revenue);

Reasons:

CrossTable(Date, Value, 5)

LOAD EMPLOYEE,

BOSS,

MANAGER,

DIRECTOR,

[Phase/Matter Codes],

[42517],

[42518]

FROM

[Test1 (1)NAMES.xlsx]

(ooxml, embedded labels, table is Reasons);

Join (Revenue)

LOAD EMPLOYEE,

BOSS,

MANAGER,

DIRECTOR,

[Phase/Matter Codes],

Date(Num#(Date)) as [GL Date],

Value

Resident Reasons;

FinalTable:

LOAD EMPLOYEE,

BOSS,

MANAGER,

DIRECTOR,

[Phase/Matter Codes],

[GL Date],

If(Len(Trim([Actual Amount])) = 0, Value, [Actual Amount]) as [Actual Amount]

Resident Revenue;

DROP Table Reasons, Revenue;

Re: ODBC+Vlookup--Scripting Help

Wed, 01 Jun 2016 19:52:22 GMT

Sunny--

I made a mistake--I did not include multiple dates of the given Phase/Matter codes in the first example.

In the first example I sent, I provided one instance of Phase/Matter Codes (ie; W002, W003, W004 etc...) with an Actual Amount. In my master data, I have multiple of the same Phase/Matter Codes (W002 listed 50+ times with different Actual Amounts).

The beginning screenshot had one Actual Amount per Phase/Matter Code. See W002 for an Actual Amount of -1201.00.

My master data contains multiple Phase/Matter Codes per date. In the below example I have attached, W002 Phase/Matter Code is listed in all of May. The problem occurring is the total sum in your current solution is listed as '-', when the total is 6727.1 for that day.

^ For example, W002 for 5/1/2016 should have a sum of 6727.1 for that day, but it is showing up as '-'. The grand total is correct with the 6727.1

I have tried fixing different equations, but i'm not having success.

As always, thank you again for your help!

Re: ODBC+Vlookup--Scripting Help

sunny_talwar — Wed, 01 Jun 2016 20:17:49 GMT

Try this:

=If(Sum(Aggr(NODISTINCT Sum(If(IsNum([Actual Amount]), [Actual Amount])), [Phase/Matter Codes])) <> 0,

If(SecondaryDimensionality() = 0 or Dimensionality() = 0, Sum(If(IsNum([Actual Amount]), [Actual Amount])),

If(IsText([Actual Amount]), [Actual Amount], Sum([Actual Amount]))))

Re: ODBC+Vlookup--Scripting Help

Wed, 01 Jun 2016 20:50:45 GMT

That is correct--thank you!!

When I add another Phase/Matter Codes in the Reasons tab, for example W003, the pivot table is showing them as '-' instead of the reasons listed per date. W003 does not have any Actual Amounts (yet)--so I would like to show the reasons..

Should be my last question!

Re: ODBC+Vlookup--Scripting Help

sunny_talwar — Wed, 01 Jun 2016 21:31:11 GMT

May be this

=If(Sum(TOTAL Aggr(NODISTINCT Sum(If(IsNum([Actual Amount]), [Actual Amount])), [Phase/Matter Codes])) <> 0,

If(SecondaryDimensionality() = 0 or Dimensionality() = 0, Sum(If(IsNum([Actual Amount]), [Actual Amount])),

If(IsText([Actual Amount]), [Actual Amount], Sum([Actual Amount]))))