topic Re: File wizard transformation error in QlikView

File wizard transformation error

Tue, 23 Feb 2016 04:41:32 GMT

Hi,

Can someone please try and help me to work out what's wrong with my script?

I'm having trouble getting the correct sum of forecast and growth value from the data. I think it may have something to do with the way I did the file transformation from in file wizard but I can't work out what went wrong.

As you can see the snapshot from my QVW file, my table shows that the sum of growth against Vendor FF for Customer1 in FY14 is $1,945,765.09 and sum of forecast is $1,945,765.09

However, if you look at my raw data, sum of growth for Vendor FF for customer 1 in FY14 should be $327,488.29 same goes to sum of forecast $327,488.29

Thanks in advance!

Re: File wizard transformation error

saurabhwadhwa — Tue, 23 Feb 2016 08:33:30 GMT

Hi Susan ,

if you see the attached image , spend is at month level where as growth and forecast are at quarter level .

Regards,
Saurabh

Re: File wizard transformation error

PrashantSangle — Tue, 23 Feb 2016 08:42:10 GMT

Hi,

can you post your script since I working on Personal edition so not able to open your QVW

Regards

Re: File wizard transformation error

settu_periasamy — Tue, 23 Feb 2016 08:49:31 GMT

Hi Susan,

There are duplicate value in Growth and Forecast Data..

Try with

SUM(Distinct Growth)

SUM(Distinct Forecast)

you will get the Desired output. You can check the Frequency to see this..

Re: File wizard transformation error

saurabhwadhwa — Tue, 23 Feb 2016 08:49:47 GMT

FYI.. directory; FOR EACH vFile in FileList('D:\Users\d642527\Desktop\Example\*.xlsx'); ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)]; Sheets: SQLTABLES; DISCONNECT; For i = 0 To NoOfRows('Sheets')-1 Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1); //============================================ Datatable: CrossTable(Date, Data, 5) LOAD '$(vSheet)' as Customer, * FROM [$(vFile)] (ooxml, embedded labels, table is $(vSheet)) WHERE(not IsNull(VENDOR)); Next; DROP Table Sheets; Next; Final: LOAD Customer, VENDOR, TYPE as [Project Type], Forecast, Growth, Date(Num#(Date)) AS Reporting_Date, //Date(Date) AS [Reporting Calendar Date], Date(Floor(Num#(Date)),'MMM-YY') as MonthYear, Year(Date(Num#(Date,'MMM-YY'))) as Year, Month(Date(Num#(Date,'MMM-YY'))) as Month, if(Month(Date(Num#(Date,'MMM-YY')))='Jan','Q3', if(Month(Date(Num#(Date,'MMM-YY')))='Feb','Q3', if(Month(Date(Num#(Date,'MMM-YY')))='Mar','Q3', if(Month(Date(Num#(Date,'MMM-YY')))='Apr','Q4', if(Month(Date(Num#(Date,'MMM-YY')))='May','Q4', if(Month(Date(Num#(Date,'MMM-YY')))='Jun','Q4', if(Month(Date(Num#(Date,'MMM-YY')))='Jul','Q1', if(Month(Date(Num#(Date,'MMM-YY')))='Aug','Q1', if(Month(Date(Num#(Date,'MMM-YY')))='Sep','Q1', if(Month(Date(Num#(Date,'MMM-YY')))='Oct','Q2', if(Month(Date(Num#(Date,'MMM-YY')))='Nov','Q2', if(Month(Date(Num#(Date,'MMM-YY')))='Dec','Q2')))))))))))) as Quarter, if(Date(Floor(Num#(Date)),'MMM-YY')='Jul-13','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Aug-13','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Sep-13','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Oct-13','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Nov-13','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Dec-13','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Jan-14','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Feb-14','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Mar-14','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Apr-14','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='May-14','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Jun-14','FY14', if(Date(Floor(Num#(Date)),'MMM-YY')='Jul-14','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Aug-14','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Sep-14','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Oct-14','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Nov-14','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Dec-14','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Jan-15','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Feb-15','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Mar-15','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Apr-15','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='May-15','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Jun-15','FY15', if(Date(Floor(Num#(Date)),'MMM-YY')='Jul-15','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='Aug-15','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='Sep-15','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='Oct-15','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='Nov-15','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='Dec-15','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='Jan-16','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='Feb-16','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='Mar-16','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='Apr-16','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='May-16','FY16', if(Date(Floor(Num#(Date)),'MMM-YY')='Jun-16','FY16', )))))))))))))))))))))))))))))))))))) as FY, Data as Spend Resident Datatable; Drop Table Datatable; //=========================== // DATE(DATE#(left(FileName(),10),'YYYY-MM-DD'),'YYYY/MM/DD') as [Reporting Calendar Date], // //DATE(DATE#(LEFT(RIGHT(FileName(),19),10),’YYYY-MM-DD’),’'YYYY/MM/DD'’) as FileDate, // today()-[PO Date] as [PO Age Days], // if(today()-[PO Date]>180,'> 6 Months', // if(today()-[PO Date]>90,'3-6 Months','< 3 Months')) as [PO Age], //==========================

Re: File wizard transformation error

settu_periasamy — Tue, 23 Feb 2016 08:51:21 GMT

Hi dreamer4‌

PFA

Re: File wizard transformation error

senpradip007 — Tue, 23 Feb 2016 09:26:19 GMT

Have a look at this.

Re: File wizard transformation error

settu_periasamy — Tue, 23 Feb 2016 10:31:56 GMT

Hi Susan,

Just a suggestion, Instead of using Multiple if's you can create two Mapping Load Table and use it..

You can the add the data easily when you are using inline Load (small table). In your script, I modified the below..

directory;

FOR EACH vFile in FileList('D:\Users\d642527\Desktop\Example\*.xlsx');

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
     Sheets:
     SQLTABLES;
     DISCONNECT;
  For i = 0 To NoOfRows('Sheets')-1

          Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);


//============================================
Datatable:
CrossTable(Date, Data, 5)
LOAD '$(vSheet)' as Customer, 
  *
FROM
[$(vFile)]
(ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(VENDOR)); 
Next;
DROP Table Sheets;
Next;

Mapping_FY:
Mapping LOAD * INLINE [
    MonthYear, FY
    Jan-14, FY14
    Feb-14, FY14
    Mar-14, FY14
    Apr-14, FY14
    May-14, FY14
    Jun-14, FY14
    Dec-13, FY14
    Jul-13, FY14
    Aug-13, FY14
    Oct-13, FY14
    Nov-13, FY14
    Aug-14, FY15
    Dec-14, FY15
    Mar-15, FY15
    Apr-15, FY15
    Jul-14, FY15
    Sep-14, FY15
    Oct-14, FY15
    Jun-15, FY15
    Jan-15, FY15
    May-15, FY15
    Nov-14, FY15
    Sep-13, FY14
    Feb-15, FY15
];

Mapping_Qtr:
Mapping LOAD * INLINE [
    Month, Quarter
    1, Q3
    2, Q3
    3, Q3
    4, Q4
    5, Q4
    6, Q4
    7, Q1
    8, Q1
    9, Q1
    10, Q2
    11, Q2
    12, Q2
];

Final:
LOAD Customer,
  VENDOR,
  TYPE as [Project Type],
  Forecast,
        Growth,
  Date(Num#(Date)) AS Reporting_Date,
  //Date(Date) AS [Reporting Calendar Date],
  Date(Floor(Num#(Date)),'MMM-YY') as MonthYear,
  Year(Date(Num#(Date,'MMM-YY'))) as Year,
  Month(Date(Num#(Date,'MMM-YY'))) as Month,
  ApplyMap('Mapping_Qtr',Month(Date(Num#(Date,'MMM-YY')))) as Quarter,
  ApplyMap('Mapping_FY',
  text(Date(Floor(Num#(Date)),'MMM-YY')) ) as FY,
  Data as Spend
Resident Datatable;
Drop Table Datatable;

Re: File wizard transformation error

Tue, 23 Feb 2016 13:32:10 GMT

Hi Settu,

hmm something doesn't seem quite right. Most of the values seem to be pulling correctly, however some are still incorrect. For example if you select vendor B, FY14 and Customer 1, you will get the below output, whereas in the raw data if you look at the FY1314_Report. Vendor B should have forecast budget of $48,471.58 and Growth of $7971.58

Appreciate your help!

Re: File wizard transformation error

Tue, 23 Feb 2016 13:34:02 GMT

Hi Pradip,

You had the same suggestion as Settu, however something doesn't seem quite right. Most of the values seem to be pulling correctly, however some are still incorrect.

For example if you select vendor B, FY14 and Customer 1, you will get the below output, whereas in the raw data if you look at the FY1314_Report. Vendor B should have forecast budget of $48,471.58 and Growth of $7971.58

Appreciate your help too!

Re: File wizard transformation error

Tue, 23 Feb 2016 13:34:46 GMT

Thanks Settu! The mapping is very helpful! will make my script neater. Cheers

Re: File wizard transformation error

Wed, 24 Feb 2016 01:13:16 GMT

Hi Saurabh,

So what should I do to change it?

Thanks,

Susan

Re: File wizard transformation error

PradeepReddy — Wed, 24 Feb 2016 05:36:10 GMT

If you take distinct, and if multiple vendors have the same Forecast value, then it will consider only once instead of two.

So I tried to avoid the use of distinct in the calculation,see the attachment, it might be helpful for you... it is the extension of what settu.periyasamy did earlier. Hope it will help you..

Re: File wizard transformation error

PradeepReddy — Wed, 24 Feb 2016 12:29:19 GMT

Check with your team, to get more details..

check whether have to showcase the forecast/growth value at month level or Year level..?.

If Month level, u have to take the monthly forecast/growth value in data source.

Re: File wizard transformation error

Wed, 24 Feb 2016 12:56:55 GMT

hmmmm some figures are still incorrect......