Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
_AnonymousUser
Specialist III
Specialist III

Insert every 100 row in table

Hi,
I have a problem statement.
I have X number of rows from input and I have to pass 1st row,100th row, 200th row and so on till last record.
Last record should also be insert in target table.
Thanks,
Saurabh
Labels (2)
4 Replies
JR1
Creator III
Creator III

Hi,
Please answer the following questions first as a possible solution depends on the components to be used:

What is the source of your data? A delimited file? A database table? Is it the output of an already existing part of your job?
What is the target? Is it an Oracle or MySQL table? Is it something different?

Regards, Joachim
cterenzi
Specialist
Specialist

Append a row number with simple sequencer to your input data using a tMap formula of:
Numeric.sequence("s1",0,1)
Your input component should set a variable NB_LINE that you can reference to get the row count.  The exact variable name varies depending on the name of your input component:
((Integer)globalMap.get("tPostgresqlInput_1_NB_LINE"))
I'm not sure if you can reference the output of that sequence in the same tMap, but if not, add a second tMap with a formula:
(rownum % 100 == 0 || rownum == (Integer)globalMap.get("tPostgresqlInput_1_NB_LINE")) ? "KEEP" : "REJECT"
A tFilter will reduce the set down to the records to keep, then a database output component can store them.
JR1
Creator III
Creator III

If you follow the solution suggested above, you will have to add a condition for the first record as well. Put this in the tMap expression filter (top right of the output - arrow with the "+" sign on top):
rownum % 100 == 0 || rownum == 1 || rownum == ((Integer)globalMap.get("tPostgresqlInput_1_NB_LINE"))
But again, the exact setup depends on your input and output components.
cterenzi
Specialist
Specialist

Yes, I tried to get cheeky and fix the first row problem by starting the sequencer at 0.  In Java, 0 mod anything returns 0.  However, the rest of the rows would then be off by one, so you'd effectively be inserting rows 99, 199, 299, etc.