topic Re: Help using cross tables? Or problems with data retrieval from Excel in QlikView

Help using cross tables? Or problems with data retrieval from Excel

Mon, 25 Jun 2012 01:51:00 GMT

Hey,

I have a spreadsheet containing forecasts that I want to compare against sales by month.

I've then used a cross table to bring it in as that's what I was told I needed to do for spreadsheets of this nature.

Here is a screenshot of the script

And finally this is how the expressions look.

My problem is that some of the item codes are missing the forecast data, while alot of the other products areshowing the right amount.

Eg. Look at FAA and then look at FAA on the top picture of the spreadsheet. There's nothing different about it, but it just won't bring the forecast data through, yet some other items look to work perfectly fine.

Can anyone shed some light on this situation?

Re: Help using cross tables? Or problems with data retrieval from Excel

Mon, 25 Jun 2012 03:55:30 GMT

Hi,

Propably you already tried this:

1. To ensure that forecast data is correct disable first expression and validate.

2. If the numbers are incorrect, some issues with the data model (like joins can create duplicates).

Can you share the table viewer?

Regards,

Kiran Rokkam.

Re: Help using cross tables? Or problems with data retrieval from Excel

Mon, 25 Jun 2012 04:09:36 GMT

Alright, a few more things which might help find the answer to my problem.

When the 01/05/2012 and 01/06/2012 forecasts are identical, the table will show the figures, although they are really incosistent and some item codes are missing and some forecasts are missing for one of the months etc.

Now when I change the figures for 01/06/2012 to be different from May's, then the data disappears and the table shows nothing.

I have attached a screenshot of the table viewer.

Re: Help using cross tables? Or problems with data retrieval from Excel

Mon, 25 Jun 2012 04:20:05 GMT

Hi,

From the table viewer the problem seems to be clear. You have forecast value for each item and FCDate. In the pivot table you used MthNum which is given from a different table with item code and monthnum based join.

Hence for each item, irrespective of the MthNum there would be a multiple FC if the values are different. To see your problem clearly please check out with the expression of FC as Sum(FC). If your cardinality is correct you would get the same exact values.

Solution: Concatenate forecasts table to facts table with FCDate as Mth.

Regards,

Kiran rokkam.

Re: Help using cross tables? Or problems with data retrieval from Excel

Mon, 25 Jun 2012 04:24:27 GMT

Yeah, I use MthNumber because when I have the sales quantity in the table it uses that field to display.

My next question is,... How do I concatenate the forecasts table to facts table with FCDate as Mth?

Sorry, Im new to Qlikview and my SQL code skills are not very strong.

Thanks for all your help.

Re: Help using cross tables? Or problems with data retrieval from Excel

Mon, 25 Jun 2012 04:52:31 GMT

Basically you need to convert FCDate to Mth with the same forma and then concatenate. Ofcourse, you need to have the values in Mth Table as well.

Enclosed document will have some examples for your reference.

Regards,

Kiran Rokkam.