Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
saurabhwadhwa
Partner - Contributor III
Partner - Contributor III

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

PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
settu_periasamy
Master III
Master III

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

saurabhwadhwa
Partner - Contributor III
Partner - Contributor III

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

settu_periasamy
Master III
Master III

Hi dreamer4

PFA

senpradip007
Specialist III
Specialist III

Have a look at this.

settu_periasamy
Master III
Master III

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;

Not applicable
Author

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!

Not applicable
Author

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!