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

[Toman]

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

[PriyankaShivhare · Accepted solution]

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:

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

[Toman]

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