Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Toman
Contributor
Contributor

Using CrossTable for previously loaded data that have been LIMITED (number of rows)

Dear Qlikers,

I am having an issue I can't find a solution for.

I have an excel file where I need to limit the number of rows loaded - so I did by increasing the Header and using First 3 or WHERE RecNo() <=3.

So I have something like this:

BRTable:
LOAD
F1,      // here are stored names that have corresponding money values under each date column
"2019-01-07",
"2019-01-14",
"2019-01-21",
"2019-01-28",
"2019-02-04",
"2019-02-11",
"2019-02-18"

FROM [lib://AttachedFiles/reportBR.xlsx]
(ooxml, embedded labels, header is 8 lines, table is Summary) WHERE RecNo() <=3;

When I do this, I have 3 rows loaded and that is exactly what I need.

HOWEVER,

Since the table is poorly done for the desired usage, I wanted to use CrossTable to transform the dates into a single field to have 3 columns - Name (names under "F1"), Date, Monetary value),  which I did by :

CrossBRTable:
CrossTable(CrossDate, Money_Value)
LOAD
F1 as Name,
"2019-01-07",
"2019-01-14",
"2019-01-21",
"2019-01-28",
"2019-02-04",
"2019-02-11",
"2019-02-18"
resident BRTable;

When I do this, my newest CrossBRTable does not have the limited number of rows (it has ALL 7 rows instead of 3) and therefore, I have there Names (F1) that I didn't want to include. When I tried to limit Crosstable by using First 3 etc, it limits the number of DATES to 3, which is also wrong...

My logic behind this was: "I load my source-Table with limiting the Data (rows) - Then I load this Limited data again using resident Load to use Crosstable and therefore, I will transform my already limited Table to the desired one".

But that does not work. 

Could you people help me with this one? Maybe I am missing something small - I am fairly new to Qlik but I am trying to do my best.

 

Thanks

Labels (1)
1 Solution

Accepted Solutions
PriyankaShivhare
Creator II
Creator II

Hi,

i have tried to replicate your scenario and gives me the expected result.

BRTable:
LOAD
F1, // here are stored names that have corresponding money values under each date column
"2019-01-07",
"2019-01-14",
"2019-01-21",
"2019-01-28",
"2019-02-04",
"2019-02-11",
"2019-02-18"
inline [
F1,"2019-01-07","2019-01-14","2019-01-21","2019-01-28","2019-02-04","2019-02-11","2019-02-18"
A,10,12,14,16,18,20,22
B,24,26,28,30,32,34,36
C,10,12,14,16,18,20,22
D,24,26,28,30,32,34,36
E,10,12,14,16,18,20,22
F,24,26,28,30,32,34,36
G,10,12,14,16,18,20,22
H,24,26,28,30,32,34,36
]
where recno()<=3;


CrossBRTable:
CrossTable(CrossDate, Money_Value)
LOAD
F1 as Name,
"2019-01-07",
"2019-01-14",
"2019-01-21",
"2019-01-28",
"2019-02-04",
"2019-02-11",
"2019-02-18"
resident BRTable;

 

Final Table:

chouksey17_0-1602509939305.png

Let me know the sample data so tht we can work it out for u

View solution in original post

2 Replies
PriyankaShivhare
Creator II
Creator II

Hi,

i have tried to replicate your scenario and gives me the expected result.

BRTable:
LOAD
F1, // here are stored names that have corresponding money values under each date column
"2019-01-07",
"2019-01-14",
"2019-01-21",
"2019-01-28",
"2019-02-04",
"2019-02-11",
"2019-02-18"
inline [
F1,"2019-01-07","2019-01-14","2019-01-21","2019-01-28","2019-02-04","2019-02-11","2019-02-18"
A,10,12,14,16,18,20,22
B,24,26,28,30,32,34,36
C,10,12,14,16,18,20,22
D,24,26,28,30,32,34,36
E,10,12,14,16,18,20,22
F,24,26,28,30,32,34,36
G,10,12,14,16,18,20,22
H,24,26,28,30,32,34,36
]
where recno()<=3;


CrossBRTable:
CrossTable(CrossDate, Money_Value)
LOAD
F1 as Name,
"2019-01-07",
"2019-01-14",
"2019-01-21",
"2019-01-28",
"2019-02-04",
"2019-02-11",
"2019-02-18"
resident BRTable;

 

Final Table:

chouksey17_0-1602509939305.png

Let me know the sample data so tht we can work it out for u

Toman
Contributor
Contributor
Author

Well, @PriyankaShivhare , you were right - it does work. I guess I might have made a mistake when editing a chart or something.. I don't know.. I tried it again today and it works.. 

Well, I am sorry if I wasted your time, but I appreciate your help and that you made me re-check it again, even when for some reason I didn't see it right (in my real example, that I cannot upload cause of my firm's policy, I have more rows and dates, so maybe I misread something.. I guess I will try to test my sample before I post it for help.. 😄 :X 

 

Thanks again and sorry! 

 

Best regards

Toman