Help with Load Script -

[#1]

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

[MichaelRobertshaw]

See 'crosstable' in the QlikView Help.

[#3]

Can you please help me with the script.

[#4]

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

[martin59]

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.

[#6]

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...

[martin59 · Accepted solution]

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

[#8]

Thanks a lot Martin....

[#9]

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