topic Help with Load Script - in QlikView

Help with Load Script -

Sun, 09 Jan 2011 07:11:49 GMT

Hi All,

I have attached a image, which contains the format of my data in .csv file, please help me, how to load these data into QV, with and without test column.

Thanks in Advance

Help with Load Script -

MichaelRobertshaw — Sun, 09 Jan 2011 19:39:05 GMT

See 'crosstable' in the QlikView Help.

Help with Load Script -

Mon, 10 Jan 2011 16:35:40 GMT

Can you please help me with the script.

Help with Load Script -

Mon, 10 Jan 2011 17:02:11 GMT

Hi qlikviewgoer,

please send the data of your image or similar sample data in the original format (I assume its excel). This is a better to load and to show you how to do this.

Regards, Roland

Re. :Help with Load Script -

martin59 — Mon, 10 Jan 2011 17:31:07 GMT

Hi,

You have to develop something like this or send your file to adapt it :



temp:
CrossTable(Month, Data)
LOAD *
FROM $(Path)\$(File).xlsx (ooxml, embedded labels, table is Feuil1);

temp2:
Load
 Prod,
 if(isnum(mid(Month,2))=False() and len(trim(Month))>1,Month,peek(Month)) as Month,
 Data,
 recno() as recno
Resident temp
;

DROP TABLE temp;

temp3:
Load Prod,
 Month,
 tmpType1,
 if(len(trim(tmpType1))=0,peek(tmpType1)) as Type1,
 Type2;
Load
 Prod,
 Month,
 if(mod(recno,3)=2,Data) as tmpType1,
 if(mod(recno,3)=0,Data) as Type2
Resident temp2
Where mod(recno,3)<>1
and len(trim(Prod))>0;

DROP Table temp2;

Table:
NOCONCATENATE Load
 Prod,
 Month,
 Type1,
 Type2
Resident temp3
WHERE len(trim(Type2))>0;

DROP Table temp3;

Hope it helps you.

Help with Load Script -

Mon, 10 Jan 2011 18:07:18 GMT

Thanks for your reply... I have attached a sample .xls data file....

And also I have to remove weeks columns and divide the Hours and Revenue by number of days .

Thanks in advance...

Re. :Re: Help with Load Script -

martin59 — Tue, 11 Jan 2011 09:47:52 GMT

Here is your load script :



temp:
CrossTable(Month, Data,4)
LOAD *
FROM
$(Path)\Sample.xls
(biff, embedded labels, table is [Report Data$]);


temp2:
Load
 Reg,
 Div,
 Team,
 Member,
 if(isnum(mid(Month,2))=False() and len(trim(Month))>1,Month,peek(Month)) as Month,
 Data,
 recno() as recno
Resident temp
;

DROP TABLE temp;

temp3:
Load Reg,
 Div,
 Team,
 Member,
 Month,
 tmpHours,
 if(len(trim(tmpHours))=0,peek(tmpHours)) as Hours,
 Revenue;
Load
 Reg,
 Div,
 Team,
 Member,
 Month,
 if(mod(recno,3)=2,Data) as tmpHours,
 if(mod(recno,3)=0,Data) as Revenue
Resident temp2
Where mod(recno,3)<>1
and len(trim(Reg))>0;

DROP Table temp2;

Table:
NOCONCATENATE Load
 Reg,
 Div,
 Team,
 Member,
 Month,
 Hours,
 Revenue
Resident temp3
WHERE len(trim(Revenue))>0;

DROP Table temp3;

Hope it helps you

Re. :Re: Help with Load Script -

Tue, 11 Jan 2011 17:52:46 GMT

Thanks a lot Martin....

Re. :Re: Help with Load Script -

Tue, 11 Jan 2011 18:01:19 GMT

And also can you please tell me how to create dates column for this table...i.e.

I should divide the Hours, Revenue by number of days in month Ex: for Jan the data should be divided by 31 (# of days in jan), 28 for Feb ...and sum by weekly... if the jan month ends in middle of the week then only calculate for jan and remaining for feb