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; 

[hic]

I think that the proper way to do this is to do it in multiple steps. First, loop over the files (no crosstable), loading also the file name:

     '$(vFile)' as FileName

Then make a resident load using the crosstable and extracting the year from the file name. The Mid() function is just an example. You may need a combination of Mid(), Index() and Subfield():

CrossTable(Month, Sales, 3)

Load Mid(FileName, x, y) as Year  ... Resident ...;

Finally, in a third step, create a proper date from year and month. And drop the temp tables.

HIC

[qlikviewforum]

Thanks Henric

When I am doing the Cross Table from the resident table how would I pass the value for Month and Sales in "CrossTable(Month, Sales, 3)"? Please help!

[hic]

Depends on how your data looks. Most likely you should use something similar to

     Crosstable (Month,Sales,3)

     Load Mid(FileName,...) as Year, ID1, ID2, Jan, Feb, Mar, ... Resident tmpTable ;

The Crosstable prefix should be used if you have columns that have month names and contain sales values - which I think is what you have.

HIC

[qlikviewforum]

Month and Sales column is not there in the xls. I am not sure how they got those fields. I have not used cross table much so not sure how they got it. I am not sure how they considering all the month field to Month and values of Month field to Sales. So please help!

[hic]

Post some data please

HIC

[qlikviewforum]

Sorry can't upload any files due to some reasons!

[hic]

Then it will be difficult to help you...

My point is anyway that you need to do the following three steps:

1. Concatenate the files using a For-Next loop. Create a field from the file name. The fields Month and Sales do not exist, but you have one column per month.
2. Use the Crosstable prefix on table 1) to create the fields Month and Sales.
3. Use an additional pass through the table to create a date form the year (hidden in the file name) and the month.

HIC

[qlikviewforum]

I am stuck in point 2!

Major concern for me was I am not aware to create the fields for Month and Sales. See if you can help me on this? Please

[hic]

The Crosstable prefix will create these fields. All you need to do is to write "Crosstable (Month, Sales, 2)" in front of the Load.

Whether you should use 2 or some other number, depends on how many columns you have before the January column.

HIC