Getting FileName as seperate value while using crosstable

[qlikviewforum]

Please help me on this. I have commented my requirement

in the code. Hope it is clear! Please let me know if you have any questions.

  
//In the Month field we don't have Year. We have values something like MMM-DD


For each vFile in Filelist ('C\Data\*.xls*')
For i = 1 to 3
Temp:
Crosstable (Month,Sales,2)
LOAD *
FROM
[$(vFile)]
(biff, embedded labels, table is @$(i))
;

next i
Next vFileName

let vYear = '-' & year(Today()); //As we dont have year in the Month field we used this variable to get the year. 
//But the problem is as considers only the current year. 2013 will also will be considered as 2014.
//So instead of this we would like to consider year from the filename. Filename is something like Jan-2013 Data File.xls
//But the issue is we are using the Cross table in the aboe for loop. So how could bring the year and add with month

Temp1:
Load
Date(Date#(Period & $(vYr) ,'MMM-DD-YYYY'),'MM/DD/YYYY') as Period
Resident
Temp; 

[qlikviewforum]

Sorry if my questions are silly.

I would like to know how it will know which fields to be considered as Month? Also how to ignore unwanted fields?

[hic]

If you have a "Crosstable (Month, Sales, 2)", then

• it will use the first 2 columns as keys (could be salesman, product, region, customer, etc.)
• it will use all other columns to create the "Sales"
• it will use all other column headers to create the "Months"

If you want to ignore some fields, you should just not list them in the Load.

HIC

[qlikviewforum]

Hi Henric,

Thank you!

I am unable to achieve it yet. May be I am missing some basic thing. I think I need to work on this and put some effort to achieve it. Thanks for all your effort! Will update once it is is achieved.

Thanks once again!

[qlikviewforum]

Once more update,

As per your instruction when I am trying to load all the data from the excel it getting stuck. I think this could be because of the synthetic keys. Any advice on this please?

[hic]

Load just a small amount of data, and QlikView will not hang on the Synthetic keys. Then you can correct the script and remove the Synthetic keys.

HIC

[qlikviewforum]

I agree with you. I loaded with 10 records in debug mode and it still hangs. Any advice?

[hic]

If you can upload just a small data sample, I could take a look at it. But without looking at data, it is impossible to say.

HIC