Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

File wizard transformation error

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!

14 Replies
Highlighted
Partner
Partner

Re: File wizard transformation error

Hi Susan ,

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

Capture.PNG

Regards,
Saurabh

Highlighted

Re: File wizard transformation error

Hi,

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted

Re: File wizard transformation error

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..

Capture.JPG

Highlighted
Partner
Partner

Re: File wizard transformation error

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],  //==========================

Highlighted

Re: File wizard transformation error

Hi dreamer4

PFA

Highlighted
Specialist III
Specialist III

Re: File wizard transformation error

Have a look at this.

Highlighted

Re: File wizard transformation error

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;

Highlighted
Not applicable

Re: File wizard transformation error

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!

Highlighted
Not applicable

Re: File wizard transformation error

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!