topic Re: Getting FileName as seperate value while using crosstable in QlikView

Getting FileName as seperate value while using crosstable

qlikviewforum — Tue, 04 Feb 2014 10:28:53 GMT

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;

Re: Getting FileName as seperate value while using crosstable

hic — Tue, 04 Feb 2014 10:41:03 GMT

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

Re: Getting FileName as seperate value while using crosstable

qlikviewforum — Tue, 04 Feb 2014 11:26:55 GMT

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!

Re: Getting FileName as seperate value while using crosstable

hic — Tue, 04 Feb 2014 11:37:45 GMT

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

Re: Getting FileName as seperate value while using crosstable

qlikviewforum — Tue, 04 Feb 2014 11:43:34 GMT

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!

Re: Getting FileName as seperate value while using crosstable

hic — Tue, 04 Feb 2014 12:15:13 GMT

Post some data please

HIC

Re: Getting FileName as seperate value while using crosstable

qlikviewforum — Tue, 04 Feb 2014 12:29:35 GMT

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

Re: Getting FileName as seperate value while using crosstable

hic — Tue, 04 Feb 2014 12:37:11 GMT

Then it will be difficult to help you...

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

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.
Use the Crosstable prefix on table 1) to create the fields Month and Sales.
Use an additional pass through the table to create a date form the year (hidden in the file name) and the month.

HIC

Re: Getting FileName as seperate value while using crosstable

qlikviewforum — Tue, 04 Feb 2014 12:52:25 GMT

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

Re: Getting FileName as seperate value while using crosstable

hic — Tue, 04 Feb 2014 12:56:58 GMT

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

Re: Getting FileName as seperate value while using crosstable

qlikviewforum — Tue, 04 Feb 2014 14:00:01 GMT

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?

Re: Getting FileName as seperate value while using crosstable

hic — Tue, 04 Feb 2014 14:06:54 GMT

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

Re: Getting FileName as seperate value while using crosstable

qlikviewforum — Tue, 04 Feb 2014 14:20:59 GMT

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!

Re: Getting FileName as seperate value while using crosstable

qlikviewforum — Tue, 04 Feb 2014 14:40:42 GMT

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?

Re: Getting FileName as seperate value while using crosstable

hic — Tue, 04 Feb 2014 14:55:10 GMT

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

Re: Getting FileName as seperate value while using crosstable

qlikviewforum — Tue, 04 Feb 2014 15:03:03 GMT

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

Re: Getting FileName as seperate value while using crosstable

hic — Tue, 04 Feb 2014 15:31:42 GMT

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