Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with Load Script -

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

1 Solution

Accepted Solutions
martin59
Specialist II
Specialist II

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



View solution in original post

8 Replies
MichaelRobertshaw

See 'crosstable' in the QlikView Help.

Not applicable
Author

Can you please help me with the script.

Not applicable
Author

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
Specialist II
Specialist II

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.



Not applicable
Author

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
Specialist II
Specialist II

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



View solution in original post

Not applicable
Author

Thanks a lot Martin....

Not applicable
Author

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