Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
josemaria_cabre
Contributor III
Contributor III

Help with crosstable script

Hi,

I need help with load script, to convert this table:

TypeProfileCodePeriod01/01/1901/02/19
HA110Hi37761908,7
HB110Hi15621273
JC105Low14001500

 

Into this one:

TypeProfileCodePeriodmonthyearqtty
HA110Hijanuary20193776
HB110Hijanuary20191562
HA110Hifebruary20191908,7
HB110Hifebruary20191273
JC105Lowjanuary20191400
JC105Lowfebruary20191500

 

How can I acchieve this using crosstable function in script? I'm getting wrong values when I sum(value) with my  script code:

[table1]:
CrossTable(dimension, value)
LOAD
[Type],
[43466] as january,
[43467] as february
FROM [lib://Desktop/file.xlsx]
(ooxml, embedded labels, table is data);


[table2]:
LOAD
[Type],
[Profile],
[Code],
[Period]
FROM [lib://Desktop/file.xlsx]
(ooxml, embedded labels, table is data);

 

Thanks in advance for your help,

Jose.

Labels (2)
2 Solutions

Accepted Solutions
raji6763
Creator II
Creator II

hi @josemaria_cabre 

here is the solution 

  1. convert into cross table
  2. format the date
  3. generate month,year from date field using month(), year() function.

raji6763_1-1593678771970.png

 

 

script:

 

originalTable:
CrossTable(Date,qty,4)
LOAD
"Type",
Profile,
Code,
Period,
num("43466") as [1/1/2019],
num( "43467") as [1/2/2019]
FROM [lib://DataFiles/Crosstabqlikcommunity.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

//-----Solution-----------
loadintoone:
load * resident originalTable;
drop table originalTable;

noconcatenate
output: //get month ,year from date field

load *,
month(Date) as Month,
year(Date) as Year;
load Type,
Profile,
Code,
Period,
date(Date#((Date) ,'DD/MM/YY'),'DD/MM/YYYY') as Date,
qty
resident loadintoone;
drop table loadintoone;

drop field Date; // if you want to drop Date field



regards,

raji

View solution in original post

Taoufiq_Zarra

One solution :

Table1:
CrossTable(Date, Data, 4)
LOAD * INLINE [
    Type, Profile, Code, Period, 01/01/19, 01/02/19
    H, A, 110, Hi, 3776, "1908,7"
    H, B, 110, Hi, 1562, 1273
    J, C, 105, Low, 1400, 1500
];

table2:
noconcatenate

load Type, Profile, Code, Period, Month(Date#(Date,'DD/MM/YYYY')) as month, Year(Date#(Date,'DD/MM/YYYY')) as year,Data as qtty resident Table1;

drop table Table1;

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

5 Replies
raji6763
Creator II
Creator II

hi @josemaria_cabre 

here is the solution 

  1. convert into cross table
  2. format the date
  3. generate month,year from date field using month(), year() function.

raji6763_1-1593678771970.png

 

 

script:

 

originalTable:
CrossTable(Date,qty,4)
LOAD
"Type",
Profile,
Code,
Period,
num("43466") as [1/1/2019],
num( "43467") as [1/2/2019]
FROM [lib://DataFiles/Crosstabqlikcommunity.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

//-----Solution-----------
loadintoone:
load * resident originalTable;
drop table originalTable;

noconcatenate
output: //get month ,year from date field

load *,
month(Date) as Month,
year(Date) as Year;
load Type,
Profile,
Code,
Period,
date(Date#((Date) ,'DD/MM/YY'),'DD/MM/YYYY') as Date,
qty
resident loadintoone;
drop table loadintoone;

drop field Date; // if you want to drop Date field



regards,

raji

Taoufiq_Zarra

One solution :

Table1:
CrossTable(Date, Data, 4)
LOAD * INLINE [
    Type, Profile, Code, Period, 01/01/19, 01/02/19
    H, A, 110, Hi, 3776, "1908,7"
    H, B, 110, Hi, 1562, 1273
    J, C, 105, Low, 1400, 1500
];

table2:
noconcatenate

load Type, Profile, Code, Period, Month(Date#(Date,'DD/MM/YYYY')) as month, Year(Date#(Date,'DD/MM/YYYY')) as year,Data as qtty resident Table1;

drop table Table1;

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
josemaria_cabre
Contributor III
Contributor III
Author

Hi!

Both solutions worked perfect, thanks a lot for your help Raji and Taoufiq!

I was wondering why are you using value '4' inside the Crosstable parameters, and the purpose for the noconcatenate prefix on table2.

Best regards,

Jose

 

Taoufiq_Zarra

1-why 4 :

CrossTable starts "crossing" from the 4 column, i.e after :

Type, Profile, Code, Period

 

2-why noconcatenate

the tables with the same column name concatenate automatically, so we use Noconcatenate to avoid concatenation and to avoid deleting all the data if we do drop table1.

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
raji6763
Creator II
Creator II

finally you got answer .😊..please close this thread to click on  Accept as a solution.